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 (in the Database with the issue and not “master”).

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.

Workaround:

SSMS locking up is not the end of the world. The underlying objects that are not exclusively locked can still be used. You just can’t get SSMS to display them. You can get the tables/views list and then directly query them.

select * from information_schema.tables with (nolock)
select * from information_schema.views with (nolock)
select * from information_schema.procedures with (nolock)

--To get the stored procedure text for a stored procedure or function
EXEC sp_helptext N'schema.stored_procedurename';

Here are some additional views that may come in handy in this situation.

Advertisement