Tag: SQL Server

How to recover “Recover Pending” database in SQL Server

One of my colleague has reached out to me to recover a database which has a state as “Recover Pending”. He tried few methods explained in another blog post, however it was not a successful effort.

While I was trying to get the database with restore…with recovery, ended up with the below error message.

Msg 3148, Level 16, State 3, Line 2 This RESTORE statement is invalid in the current context. The ‘Recover Data Only’ option is only defined for secondary filegroups when the database is in an online state. When the database is in an offline stafte filegroups cannot be specified. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.

Finally, we used the below method to get the database online. Since its a testing environment and a small amount of data loss is not an issue for us, we used DBCC command to Repair_allow_data_loss. Caveat: We would not recommend to use this method for Production environment, probably, we need to restore the database from a valid backup until the point in time recovery.

ALTER DATABASE dbname SET EMERGENCY;
GO
ALTER DATABASE dbname set single_user
GO
DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE dbname set multi_user

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

ROW_NUMBER() window function in SQL Server

This blog post explains few methods to generate a running number for a SELECT query. Different SQL versions have different ways to achieve this. Let us quickly see ROW_NUMBER() window function with this blog post.

ROW_NUMBER()OVER(PARTITION BY column_list ORDER BY column_list ASC/DESC)

This returns the sequential number for rows. A Quite simple way in SQL Server so far, note that there are different ways we can generate this numbers on group or set depending on sorted manner.Let us see some of those forms in this post. As first step, let us create a SampleData table populated with few records as below.

Create Table SampleData
(
	CourseId	Int,
	CourseName	Varchar(100),
	Institute	Varchar(100),
	Fees		Int
)

Insert into SampleData
	Values  (1,'SQL Server', 'Aptech', 1000),
			(1,'SQL Server', 'WowTech', 2000),
			(2,'.NET', 'NetTechs', 6000),
			(2,'.NET', 'Aptech', 8000),
			(2,'.NET', 'SimpleLearn', 7500),
			(3,'Python', 'Aptech', 1000),
			(3,'Python', 'SimpleLearn', 1500),
			(3,'Python', 'PyLearn', 1000),
			(3,'Python', 'NetTechs', 1000),
			(3,'Python', 'WowTech', 1000)

Select * from SampleData

--Drop Table SampleData

Simple Form of generating a running number

We can create a running number column with row_number window function as below:

Select Row_Number()Over(order by (Select NULL) ASC) Rn,* From SampleData
Select Row_Number()Over(order by (Select NULL) DESC) Rn,* From SampleData

In the above two example, we can see both ASC and DESC returns the same set of ordering and running number because the order by always on NULL value which would have no effect for ASC and DESC.

Form of generating a running number based on a set of data

Yes, this is based on set or group data. For an example, if we need to generate a running number for a grouped records and so on, you can introduce the Partition by clause to the above query as below.

Select Row_Number()Over( partition by CourseId order by (Select NULL) ASC) Rn,* From SampleData

Form of generating a running number based on a set of data with a defined order of data

In the above example, we have seen the number based on data, now, we are going to quickly see the numbering is based on group of data as well as ordered in a defined way. In our example, we wanted to see the data group for CourseID should be sorted by its Fees in ascending order.

Select Row_Number()Over( partition by CourseId order by Fees ASC) Rn,* From SampleData

Hope this is helpful to understand better of ROW_NUMBER window function in SQL Server.

Hope you enjoyed this post, share your feedback in comment section. Recommending to go through “Microsoft SQL Server – Beginners Guide” series for more information.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Error: Changes to the state or options of database ‘dbname’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

One of my colleague had an issue in dropping a database in her testing environment. She was not able to drop a database as she gets an error message (as below). Let me try to provide what she tried and ended up for every ones understanding.
alter database [dbname] set multi_user with rollback immediate
Drop database [dbname]

Error Message:

Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of database ‘dbname’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

Solution:

When I analysed, I could find that the database has gone into single user mode and there was an open session on this database.Since, its a testing environment, I had killed the open session from the database and tried to drop the database by putting it multi user as first step as below and it was successful.

USE master;

DECLARE @killSessions varchar(8000) = '';  
SELECT @killSessions = @killSessions + 'kill ' + CONVERT(varchar(5), spid) + ';'  
FROM master..sysprocesses  
WHERE dbid = db_id('dbname')
EXEC(@killSessions); 

alter database [dbname] set multi_user with rollback immediate
--Drop database [dbname] /*Only if need to be dropped*/

Hope this helps if you come across similar situations.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

WITH RETURNS NULL ON NULL INPUT in SQL Server

While I was going through a review of a sql server function, it is observed there are many checks implemented if any of input parameter is null then return null. Then, it got strike on mind about “WITH RETURNS NULL ON NULL INPUT” in SQL Server. This is available in SQL server from 2015 version, however, I have not seen people used it efficiently. So thought of sharing about this intelligent way of handling such situations.

Let us start with a sample function as below:

Mighty Sample function

create or alter function fn_validateinparam(@col1 varchar(50), @col2 varchar(50))
returns  varchar(100)
as
Begin

	Declare @outputval varchar(100)
	If (@Col1 is null or @Col2 is null)
		set @outputval= NULL
	Else
		set @outputval= 'Valid params'

	return @outputval
End

Select dbo.fn_validateinparam('You','me')
Select dbo.fn_validateinparam('','')
Select dbo.fn_validateinparam('You',NULL)
Select dbo.fn_validateinparam(NULL,NULL)
Select dbo.fn_validateinparam(NULL,'me')

function using WITH RETURNS NULL ON NULL INPUT

The above is a sample function that we can see in many places to check the null param check. There is an option introduced in SQL Server 2005 “WITH RETURNS NULL ON NULL INPUT” to handle this with a grace as below. This way, if any of your input parameters is passed as NULL value, then the function will immediately return NULL value without actually invoking the body of the function. That clears the theory and its time for us to check with sample code as below.

create or alter function fn_Efficientvalidateinparam(@col1 varchar(50), @col2 varchar(50))
returns  varchar(100)
WITH RETURNS NULL ON NULL INPUT
as
Begin

	Declare @outputval varchar(100)
	If (@Col1 is null or @Col2 is null)
		set @outputval= 'Wrong params'
	Else
		set @outputval= 'Valid params'

	return @outputval
End

Select dbo.fn_Efficientvalidateinparam('You','me')
Select dbo.fn_Efficientvalidateinparam('','')
Select dbo.fn_Efficientvalidateinparam('You',NULL)
Select dbo.fn_Efficientvalidateinparam(NULL,NULL)
Select dbo.fn_Efficientvalidateinparam(NULL,'me')

Sample output

The below screenshot explains that the option returns without getting into execution of the function whenever one of the parameter is having NULL value. (Please note I used “Wrong value” to assign to the output variable to understand it actually returns in the beginning or only after the execution.)

If we have not used this technique so far, start using this one, it helps to avoid lots of junk codes in the definition that makes it clean. In addition, it has also observed a small factor of performance improvement for the function.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

How to identify redundant/duplicate indexes in SQL Server

What are redundant indexes in SQL Server?

Identifying redundant indexes is a very important task for a DBA. I personally do not find any reason of having redundant indexes on databases. Otherwise, it is an over head to the system like maintaining the index/storage space etc.

The challenge is to identify a redundant/duplicate index in SQL Server? If we do not understand the meaning of redundant/duplicate, then we might end up with removing important indexes and it can lead to performance degradation.

I would like to list out few considerations to categorize indexes as redundant; if:

1. An index has Same key columns in the same order with another index

2. An index has Key columns those are left based subset of another index

3. Meeting all of the above, ONLY for similar index types.(Clustered and no clustered are not to be considered as duplicate indexes)

4. Meeting all of the above, And the Key Columns specified with same ordering (ASC/DESC)

The below query does not identify the duplicate index, but it gives you enough information with which we can easily identify the duplicate indexes in your system.

Note:(String_AGG is new function in SQL Server 2017)

Select Object_Schema_name(ix.object_id) Schema_Name,
		Object_name(ix.object_id) Object_Name,ix.name,ix.type_desc ,
		string_agg(Cast(c.name as nvarchar(MAX)) + ' (' + case when is_descending_key = 0  then 'ASC' Else 'DESC' END + ') ',',') 
		within group(Order by ixc.key_ordinal asc) As KeyCols
From
	sys.indexes ix
	inner join sys.index_columns ixc on ix.index_id = ixc.index_id and ix.object_id = ixc.object_id
	inner join sys.columns c on ixc.object_id = c.object_id and c.column_id = ixc.column_id 
Where objectpropertyex(ix.object_id,'IsMSShipped') =0 
		And ixc.is_included_column=0 and ix.index_id <> 1 
		Group by ix.object_id,ix.name,ix.type_desc
Order by 1 asc,2 asc

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!