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 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
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.