SQL Server: SSMS Says “Lock request time out period exceeded.” – Identify Blocking Source Quickly

Time-tested approach

Although I explain the process for table expansion, the troubleshooting steps to find any blocking session is to run sp_who2 and look at the BlkBy column to get the session id that is blocking a session (assuming you can identify sessions from the information in the other columns) and examine the blocking session. Then DBCC INPUTBUFFER may be used with SessionID and RequestID to get the SQL specifics.

Using DBCC OPENTRAN

Here, I show how DBCC OPENTRAN can also be used to quickly narrow down the specific problem below.

When working with SSMS, have you run into a situation where you try to expand the list of tables and SQL Server came back with this nice message?

Lock request time out period exceeded. (.Net SqlClient Data Provider)

This is because someone is altering a table in the database you are working with and that session has reserved a schema-modification lock.

Also, you could find out what operation is causing blocking and who is behind the associated SQL so that you can talk to that person to find out when that will complete (or) to your DBA to kill some run-away DDL.

First look to see what open transactions are out there.

DBCC OPENTRAN

In my case it returned:

Transaction information for database 'DWDEV01'.

Oldest active transaction:
    SPID (server process ID): 104
    UID (user ID) : -1
    Name          : ALTER TABLE
    LSN           : (300559:16923375:1)
    Start time    : Jun 30 2014  8:13:23:910AM
    SID           : 0x01050000000000051500000011c35f730d7a5a3307e53b2b3e320100
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Right away, I was able to look at sp_who2 with the SID as parameter to find out the user associated with SID 104 (highlighted above). You could also use “Activity Monitor” or sys.dm_exec_requests to get the same.

EXEC sp_who2 104

Get the SQL details

dbcc inputbuffer(104)

The offending SQL in this case was:

ALTER TABLE ETL.TransactionPreStage

ALTER COLUMN BusinessType nvarchar(1) NULL;

I was able to contact the person reported by sp_who2 and ask how long it would take for the above SQL to complete and go from there.

To get a rough sense for how long this operation might take, I also looked at the approximate row count of the table involved with this SQL:

SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(object_id)='TransactionPreStage'

It returned 21,882,870 as the row count. The operation finished soon enough.

You could also use sp_lock and the DMVs sys.dm_tran_locks and sys.dm_tran_session_transactions but they may need more digging unless you have a canned query ready to run with all the joins in place.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s