Home > Management Studio, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > Check currently blocked processes – “sp_who_blocked !”

Check currently blocked processes – “sp_who_blocked !”


We use the undocumented stored procedure sp_who2 to check for blocked process on the server. The output returned by this stored procedure returns all processes on the server.

image

now, to find the processes which are actually blocked, you need to go through the result set, which will be almost > 50 on a normal DB server (during off-peak hours).

What we need most of the time is “Are there any processes which are currently blocked?”. To achieve this we can add a condition in sp_who2 which will restrict the result set to blocked processes:


and
blocked <> ''0''


You need to add this condition on line 246 of existing sp_who2 definition:

             spid >= ' + @charspidlow  + '
      and    spid <= ' + @charspidhigh + '
    and blocked <> ''0''
    — (Seems always auto sorted.)   order by [BlkBy] Desc

after retrieving the definition, create a new stored procedure once you add the required condition.

The output of the new stored procedure will be only thr currently blocked processes (if any):

image 

Hope This Helps!

Vishal

  1. No comments yet.
  1. No trackbacks yet.