Run this query, or remove the comments to actually create a stored proc.
Note that SQL pre-2008 requires a field to be commented out of the query, as noted inline.
/* --Create Procedure WhoLock --AS 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 --Remove this line for SQL 2005 or earlier -- ) 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
Although this is a bare metal approach to investigating sql contention, the query does show you actual login names, so it enables you to join up technical issues with affected/affecting customers.
5 thoughts on “sp_WhoLock – a T-SQL stored proc combining sp_who and sp_lock to show who’s locking what and how much”
Thanks for spending time on the computer (writing) so others don’t have to.
This is a great example for what happens if future version of the built in sp’s differ from previous ones.
hello claudia, I need your help again, the first linky number 104, does not work and so I relink and at 105, that works. please help me remove 104. thanks claudia and happy hosting.
I’ve been surfing on-line much more than three hours as of late, yet I in no way found any fascinating article like yours. It is actually pretty worth adequate for me. Personally, if all web site owners and bloggers created fantastic content material as you possibly did, the world wide web will most likely be significantly more beneficial than ever ahead of.
Mighty useful. Make no mistake, I apetcpiare it.
Comments are closed.