ALTER DATABASE failed because a lock could not be placed on database ‘dbname’. Try again later.

Problem statement

As a DBA, you will be facing the error message “ALTER DATABASE failed because a lock could not be placed on database ‘dbname’. Try again later.” often for certain actions like taking database offline/setting single user etc.

Analysis

This is due to other processes locking your current operations.

Resolution

1. Find out the query which is blocking the current operation using the below query
Live monitoring query

2. Kill the process and take the database in SINGLE_USER

KILL 191 --session id from the first query related to the database
USE MASTER
ALTER DATABASE DB905 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

3. Take the database offline

ALTER DATABASE db905 SET OFFLINE WITH ROLLBACK IMMEDIATE
Advertisements

DBMIRROR_DBM_EVENT wait type causing blocking in SQL Server

Recently, we observed lots of blocking in our production server showing the wait type as DBMIRROR_DBM_EVENT. To brief, the system is an OLTP system with lots of DML actions, ended up with lot of long running transactions and blocking the user transactions. As DBMIRROR_DBM_EVENT wait type is an internal to Microsoft and there is less documentation available on Web, we started our troubleshooting looking at the mirroring set up. Here are few points we started our analysis as below.

1. Verify DB Mirroring is working uninterrupted – if the mirroring is not working for some reason, on synchronous mode, the impact is huge, the principal server has to wait to commit.

2. Verify feasibility of DB Mirroring mode to Asynchronous – Changing Mirror mode from synchronous to Asynchronous is not a solution,however, its a workaround. Ideally, we need to identify if long running transaction/index maintenance etc caused the issue while applying the changes to Mirror.

3. Verify network latency between Principal and Mirror – The latency in the network is one of the reason that can be assessed using the perfmon counter.

Root cause and resolution

In our case, the root cause was the first one. For some reason, the mirroring has been broken, no endpoint was accepting the connection from the principal server. This lead to a situation on the principal server each time a transaction waits for its log (LSN) to be hardened on the mirror.Once we reestablished the mirroring, the wait type is removed and no blocking observed further.

How to get the status of Mirroring:


SELECT
DB_NAME(DATABASE_ID) AS [DATABASENAME],
CASE WHEN MIRRORING_GUID IS NOT NULL THEN 'MIRRORING IS ON' ELSE 'NO MIRROR CONFIGURED' END AS [ISMIRRORON],
[MIRRORING_STATE_DESC],
CASE WHEN MIRRORING_SAFETY_LEVEL=1 THEN 'HIGH PERFORMANCE' WHEN MIRRORING_SAFETY_LEVEL=2 THEN 'HIGH SAFETY' ELSE NULL END AS [MIRRORSAFETY],
MIRRORING_ROLE_DESC,MIRRORING_PARTNER_INSTANCE AS [MIRRORSERVER]
,MIRRORING_PARTNER_NAME AS [PARTNER NAME]
,MIRRORING_ROLE_DESC AS [MIRROR ROLE]  
,MIRRORING_SAFETY_LEVEL_DESC AS [SAFETY LEVEL]
,MIRRORING_WITNESS_NAME AS [WITNESS]
,MIRRORING_CONNECTION_TIMEOUT AS [TIMEOUT(SEC)]
FROM SYS.DATABASE_MIRRORING

See Also:
https://blogs.msdn.microsoft.com/grahamk/2011/01/10/sql-server-blocking-caused-by-database-mirroring-wait-type-dbmirror_dbm_event/

How do we recover unsaved query window in SQL Server Management Studio?

How do we recover the unsaved query window in SQL Server Management Studio?

This is a simple tip to recover your unsaved query window in SSMS.

As a SQL server professional, most of us will work closely with SSMS in a daily basis.At times, we forget to save our work in the management studio and for some reason if the query window closes/SSMS crashes, we will be in a position of “lost everything”. You may be questioned on your best practises. Here is a way to recover the SSMS window content which would save your time and effort.

You can verify the below path and get the files:

C:\Users\<>\Documents<>\SQL Server Management Studio\Backup Files

The above path may change depends on the windows version.

How does it happen?

There is a setting in Management Studio that allows the Autorecover option enabled by default. We can modify the default settings as per our need to reduce the risk of loss.

Go to SSMS -> Tools -> Options -> Environment -> AutoRecover

There are two different settings:

1) Save AutoRecover Information Every Minutes

This option will save the SQL Query window file at certain interval specified. You can set this value carefully on how frequent that you need to save your SSMS cahnges.

2) Keep AutoRecover Information for Days

This option will preserve the AutoRecovery information for specified days.

Error Message – The index ” is dependent on column ”.ALTER TABLE ALTER COLUMN ” failed because one or more objects access this column.

It is always annoying when we get a request to change the datatype of an existing column for many reason. The changes are due to business reason or bad design or anything. But, when we change to accommodate, we need to do lots of analysis to ascertain the impact of the change.

Here, we are going to explain a case, changing datatype size varchar (n) to varchar(max). This blog ONLY explains the context of changing the datatype not the impact of the change in terms of performance.


Create Table ColumnDatatypeChange (Col1 int,Col2 varchar(1))
Insert into ColumnDatatypeChange Select 1 , 'L'
create Index IX_ColumnDatatypeChange on ColumnDatatypeChange(Col1) include(Col2)
Alter Table ColumnDatatypeChange Alter column Col2 Varchar(MAX) -- This statement will fail

On executing the below snippet, we get the below error message. (Please note that an index is dependent on column)

Error Message:
Msg 5074, Level 16, State 1, Line 8
The index ‘IX_ColumnDatatypeChange’ is dependent on column ‘Col2’.
Msg 4922, Level 16, State 9, Line 8
ALTER TABLE ALTER COLUMN Col2 failed because one or more objects access this column.

However, when we do the change from MAX to 8000, it gets executed without any issue.


Create Table ColumnDatatypeChange (Col1 int,Col2 varchar(1))
Insert into ColumnDatatypeChange Select 1 , 'L'
create Index IX_ColumnDatatypeChange on ColumnDatatypeChange(Col1) include(Col2)
Alter Table ColumnDatatypeChange Alter column Col2 Varchar(8000) -- This statement will succeed

Reason:

The reason for this behavior is due to the change from non-max type to a max type. SQL Server considers this change as dropping a column and adding a new column. Internally, the non-max and max types do not share the same storage types (Row-Overflow vs. LOB allocation units). Hence, this change needs to update every row and move the data from dropped column to newly added column. However, if you see for non-max type change, they share the same storage (either in-row or in the row-overflow allocation unit), so no issues with in-place update, that mean, no need to drop the dependent(index in this case).

Resolution:

1. Drop the index and change the column size followed by adding the index back to the table


Drop Index IX_ColumnDatatypeChange on ColumnDatatypeChange
Alter Table ColumnDatatypeChange Alter column Col2 Varchar(MAX)
create Index IX_ColumnDatatypeChange on ColumnDatatypeChange(Col1) include(Col2)

2. Analyze and try to avoid the size to MAX (if possible)

Thanks for your read and looking for your feedback!!!

New enhancement in SSMS in SQL Server

SSMS in SQL 2016 has come up with lots of new features. One of the features is enhanced scrollbar in SSMS. I personally believe the new enhancement in scrollbars will definitely help developers to work with SSMS with ease.

Let us look at the below picture to have an insight:

They are different color bocks on the right side of SSMS query window that shows the changes:
a. Green blocks show the saved lines of Query part in a window to disk
b. Yellow blocks show the query lines of unsaved/changed lines since last save to disk
c. Red blocks show any syntax errors in a query window
d. Blue block shows the current cursor location

It is definitely good to know to developers who plays around with SSMS very frequently.

Extending sp_helpindex to get more useful information about index in SQL Server

This post is to help people who had tough time to get information about index and its included columns in SQL Server. Whenever I need, I always depend on object explorer and get the definition of the index.So I thought of compiling a snippet as below that can provide these information handy.

I created a procedure called – sp_helpindexExtended as below and the Tablename as parameter(this is an optional parameter).

Procedure Definition


Create Proc sp_helpindexExtended (@TableName sysname = NULL) 
as 
Begin 
	  ;With cte as 
	  (
		 Select Object_name(A.object_id) objectname,A.name index_name,
			Lower(type_desc) + Case When IS_Unique =1 then ', unique' Else '' end +
			Case when is_primary_key =1 Then ', primary key' Else '' End+
			Case when IS_Unique_Constraint = 1 Then ',unique key' Else '' End  index_description,
			Case when is_included_column=0 then c.name  + '('+
            Case When System_type_id = User_Type_id Then Type_Name(System_type_id) Else Type_Name(User_Type_id) End +')'  Else NULL end ColumnName,
            Case when is_included_column=1 then c.name  + '('+
            Case When System_type_id = User_Type_id Then Type_Name(System_type_id) Else Type_Name(User_Type_id) End +')'  Else NULL end IncludedColumnnames
            ,Is_Unique,Is_Primary_Key,IS_Unique_Constraint,Fill_factor,key_ordinal
    From sys.indexes A  
    Inner Join sys.index_columns B On A.object_id = B.object_id And A.index_id = B.index_id 
    Inner Join sys.columns C On c.object_id = B.object_id  And C.column_id  = B.column_id 
	Where A.Object_ID = Case when @TableName is not null Then OBJECT_ID(@TableName) Else A.object_id End
	)	SELECT objectname,index_name,index_description,
       stuff(( SELECT ','+ColumnName AS [text()] FROM cte p2
          WHERE p2.objectname = p1.objectname and p2.index_name = p1.index_name
          ORDER BY key_ordinal asc FOR XML PATH('') ), 1, 1,'') AS index_keys,
		stuff( ( SELECT IsNull(','+IncludedColumnnames ,'')  FROM cte p2
          WHERE p2.objectname = p1.objectname and p2.index_name = p1.index_name
          ORDER BY key_ordinal asc FOR XML PATH('') ), 1, 1,'')  AS included_keys
		  ,fill_factor 
      FROM cte p1 GROUP BY objectname,index_name ,index_description,fill_factor 
	 order by objectname asc
End

Procedure Usage

-- To get for all tables
exec sp_helpindexextended
-- To get only for a table
exec sp_helpindexextended 'tablename'

How to extract ONLY numbers from a string in SQL Server

Here is a script to extract *ONLY* numbers from a string.


DECLARE @Sample TABLE(string VARCHAR(2000));
INSERT INTO @Sample
Values( 'hhjjj12345hhhkk'),('-7655'),('asd5-5dffgdfg105'),('hhjjj12345hhhkk'),('a1b2c3d4ee55fff666gggg7777h'),('Al1')
,('2Bob$'),('!Carl%'),('%D#?:"{}|\][+_)(*&^	%$#@!~`_+?>a*vi()(d&*(&*#?:"{}|\][J+o_n)es(*&^%$#@!~`_+?><,./;:"' ),('    Eddie     ')
;WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
FinalOutput AS (
SELECT string, (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^0-9]'
                       THEN SUBSTRING(string, n, 1)
                      ELSE ''
                  END + ''
           FROM Nums
           WHERE n <= LEN(string)
           FOR XML PATH('')) AS stringout
FROM @Sample)
SELECT string, stringout FROM FinalOutput;