I am working on SQL Server 2005 SP3. From Activity monitor, I notice a lot of processors were locked in suspended mode. do you know how to find who locks my processes?
+3
A:
exec sp_who
will tell you what processes are blocked by what other processes. You can also use
exec sp_lock
to see what's being locked by said blocking process.
Ralph Shillington
2009-06-16 01:26:45
For details on who's who in the blocking game, read the description of the `blk` column in `sp_who`'s result: http://msdn.microsoft.com/en-us/library/ms174313.aspx
Thomas Jung
2009-06-16 01:40:20
Calling "DBCC INPUTBUFFER(spid)" with the spid you get back from sp_who will also show the last sql statement that the process ran.
Ted Elliott
2009-06-16 02:15:00
SP_WHO2 provides more real world useful information than sp_who and sp_lock, DBCC INPUTBUFFER(SPID) is a must have to go with SP_WHO2 to see what query they are running.
DBAndrew
2009-06-16 17:21:10
A:
In addition to Ralph's excellent post, here's a good article on the various methods available to you to monitor blocking - it's by one of Microsoft's Premier Field Engineers.
Aaron Alton
2009-06-16 02:04:12
+2
A:
SP_WHO2
SP_WHO2 shows blocking and blocked by spids along with host names and much more useful information needed to track down the source.
Edit: Also if the output list is long try SP_WHO2 'active'
DBAndrew
2009-06-16 16:47:45