How to find out what is locking my tables?

Question

I have a SQL table that all of a sudden cannot return data unless I include with (nolock) on the end, which indicates some kind of lock left on my table.

I've experimented a bit with sys.dm_tran_locks to identify that there are in fact a number of locks on the table, but how do I identify what is locking them (ie the request element of the sys.dm_tran_locks)?

EDIT: I know about sp_lock for pre SQL 2005, but now that that sp is deprecated, AFAIK the right way to do this is with sys.dm_tran_locks. I'm using SQL Server 2008 R2.

Answer 1

For getting straight to "who is blocked/blocking" I combined/abbreviated sp_who and sp_lock into a single query which gives a nice overview of who has what object locked to what level.

--Create Procedure WhoLock --AS set nocount on if object_id('tempdb..#locksummary') is not null Drop table #locksummary if object_id('tempdb..#lock') is not null Drop table #lock create table #lock (    spid int,    dbid int,    objId int,    indId int,    Type char(4),    resource nchar(32),    Mode char(8),    status char(6)) Insert into #lock exec sp_lock if object_id('tempdb..#who') is not null Drop table #who create table #who (     spid int, ecid int, status char(30),             loginame char(128), hostname char(128),             blk char(5), dbname char(128), cmd char(16)             --             , request_id INT --Needed for SQL 2008 onwards             --          ) Insert into #who exec sp_who Print '-----------------------------------------' Print 'Lock Summary for ' + @@servername  + ' (excluding tempdb):' Print '-----------------------------------------' + Char(10) Select     left(loginame, 28) as loginame,      left(db_name(dbid),128) as DB,     left(object_name(objID),30) as object,     max(mode) as [ToLevel],     Count(*) as [How Many],     Max(Case When mode= 'X' Then cmd Else null End) as [Xclusive lock for command],     l.spid, hostname into #LockSummary from #lock l join #who w on l.spid= w.spid where dbID != db_id('tempdb') and l.status='GRANT' group by dbID, objID, l.spid, hostname, loginame  Select * from #LockSummary order by [ToLevel] Desc, [How Many] Desc, loginame, DB, object  Print '--------' Print 'Who is blocking:' Print '--------' + char(10) SELECT p.spid ,convert(char(12), d.name) db_name , program_name , p.loginame , convert(char(12), hostname) hostname , cmd , p.status , p.blocked , login_time , last_batch , p.spid FROM      master..sysprocesses p JOIN      master..sysdatabases d ON p.dbid =  d.dbid WHERE     EXISTS (  SELECT 1           FROM      master..sysprocesses p2           WHERE     p2.blocked = p.spid )  Print '--------' Print 'Details:' Print '--------' + char(10) Select     left(loginame, 30) as loginame,  l.spid,     left(db_name(dbid),15) as DB,     left(object_name(objID),40) as object,     mode ,     blk,     l.status from #lock l join #who w on l.spid= w.spid where dbID != db_id('tempdb') and blk <>0 Order by mode desc, blk, loginame, dbID, objID, l.status 

(For what the lock level abbreviations mean, see e.g. https://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx)

Copied from: sp_WhoLock – a T-SQL stored proc combining sp_who and sp_lock...

NB the [Xclusive lock for command] column can be misleading -- it shows the current command for that spid; but the X lock could have been triggered by an earlier command in the transaction.

Answer 2

Take a look at the following system stored procedures, which you can run in SQLServer Management Studio (SSMS):

• sp_who
• sp_lock

Also, in SSMS, you can view locks and processes in different ways:

Different versions of SSMS put the activity monitor in different places. For example, SSMS 2008 and 2012 have it in the context menu when you right-click on a server node.