How can I view currently blocked SPIDs in SQL?

Products

• IT Management Suite
• Client Management Suite

Issue/Introduction

As an Admin, how can I view blocking and possible deadlocks in the Symantec Database?

Resolution

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:

/*
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.