Tutorial :how to find who locks who in SQL Server 2005



Question:

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?


Solution:1

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.


Solution:2

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'


Solution:3

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »