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.
Subscribing will provide email updates when this Article is updated. Login is required to Subscribe
7.0 SP3, 7.0 SP2, 7.0 SP1, 7.0, 6.0 SP3, 6.0 SP2, 6.0 SP1, 6.0
Thanks for your feedback. Let us know if you have additional comments below. (requires login)
This will clear the history and restart the chat.