How can I view currently blocked SPIDs in SQL?

Article:HOWTO9817  |  Created: 2009-02-27  |  Updated: 2013-03-20  |  Article URL http://www.symantec.com/docs/HOWTO9817
Article Type
How To

Product(s)


This article applies to SQL 2005 and higher. SQL has a built-in stored procedure, sp_who2, that lists the SPIDs (processes) along with several additional fields, such as status and current command.

In SQL Management Studio, there is also an Activity Monitor, under the Management folder that provides a graphical view of SPID activity.

Below is a script that can be used when Activity Monitor is not sufficient or unavailable:

/*
HOWTO9817 SQL Process Blocks information script
Updated January, 2012
*/

set transaction isolation level read uncommitted 

select db.name [Database],
       procs1.spid SPID, procs1.blocked [Blocking SPID],
       case
              when blocked = 0
                     then '**Block Chain Root**'
              when blocked > 0
                     then 'Blocked by: ' + cast(procs1.blocked as CHAR(10))
       end [Status],
       procs1.text [Event Info], procs2.text [Blocking Event Info],
       procs1.cpu [CPU Time], procs2.cpu [Blocking CPU Time],
       procs1.physical_io [Disk IO], procs2.physical_io [Blocking Disk IO]
from 
       (
            select procs.dbid, procs.spid, procs.blocked, procs.cpu, procs.physical_io, sql.text   
           
from sys.sysprocesses procs
                     outer apply   sys.dm_exec_sql_text(procs.sql_handle) sql
       ) procs1

              join sys.sysdatabases db
                     on procs1.dbid = db.dbid
              outer apply
              (
                     select procs.spid, procs.cpu, procs.physical_io, sql.text
                     from sys.sysprocesses procs
                           outer apply   sys.dm_exec_sql_text(procs.sql_handle) sql
                     where procs.spid = procs1.blocked
              ) procs2

where procs1.blocked > 0
       --find block chain root
       or
       (
              procs1.blocked = 0
                     and procs1.spid in
                     (
                           select blocked
                           from sys.sysprocesses
                     )
       )
       and db.name = db_name()
--put block chain roots at the top
order by procs1.blocked

Additionally the attached script could be run through an SQL Job to gather data that could be used for troubleshooting.


Attachments

Blocking (Query to track).sql (3 kBytes)

Legacy ID



45830


Article URL http://www.symantec.com/docs/HOWTO9817


Terms of use for this information are found in Legal Notices