Detect blocking processes in SQL Server

this is for SQL Server 2005. in SQL Serer 2008, you have to replace sys.sysprocesses to be dbo.sysprocesses in master database.

blocking is not deadlock.

First query, it retrieves the sql statements by using cross apply. output is put into temporary table for further analysis.

SELECT
s.spid, s.blocked BlockingSPID, DB_NAME(s.dbid) DatabaseName ,
s.program_name, s.loginame, OBJECT_NAME(objectid, s.dbid) ObjectName, CAST(text AS VARCHAR(MAX)) Definition
INTO #Processes
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE
s.spid > 50

WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow)
AS
(
SELECT
s.SPID, s.BlockingSPID, s.Definition,
ROW_NUMBER() OVER(ORDER BY s.SPID),
0 AS LevelRow
FROM
#Processes s
JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
WHERE
s.BlockingSPID = 0
UNION ALL
SELECT
r.SPID, r.BlockingSPID, r.Definition,
d.RowNo,
d.LevelRow + 1
FROM
#Processes r
JOIN Blocking d ON r.BlockingSPID = d.SPID
WHERE
r.BlockingSPID > 0
)
SELECT * FROM Blocking
ORDER BY RowNo, LevelRow