Blocking Chains

-- http://thesqlguy.wordpress.com/2010/11/15/sql-2005-blocking-chains-a-friendly-display-using-cte-and-recursion/
--Best to output results to text rather than grid -- SQL 2005 and above only

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#Processes') IS NOT NULL
    DROP TABLE #Processes

DECLARE @results TABLE (
    id INT IDENTITY(1,1),
    DB_Name VARCHAR(500),
    BlockingSPID INT, SPID INT,
    BlockingStatement VARCHAR(MAX),
    RowNo INT, LevelRow INT,
    hostname sysname
)

SELECT
    s.spid,
    BlockingSPID = s.blocked,
    DatabaseName = DB_NAME(s.dbid),
    s.program_name,
    s.loginame,
    ObjectName = OBJECT_NAME(objectid, s.dbid),
    Definition = CAST(text AS VARCHAR(MAX)),
    hostname
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, DB_Name, Hostname)
AS
(
    SELECT
        s.SPID,
        s.BlockingSPID,
        s.Definition,
        ROW_NUMBER() OVER(ORDER BY s.SPID) AS RowNo,
        0 AS LevelRow,
        s.DatabaseName AS DB_Name,
        s1.hostname
    FROM #Processes s
        INNER 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,
        r.DatabaseName AS DB_Name,
        d.Hostname
    FROM #Processes r
        INNER JOIN Blocking d ON r.BlockingSPID = d.SPID
    WHERE r.BlockingSPID > 0
)
INSERT INTO @results (
    [DB_Name],
    [BlockingSPID],
    [SPID],
    [BlockingStatement],
    [RowNo],
    [LevelRow],
    [Hostname]
)
SELECT
    MIN(DB_NAME) AS DB_Name,
    BlockingSPID,
    SPID,
    BlockingStatement,
    MIN(RowNo),
    LevelRow,
    [Hostname]
FROM Blocking
GROUP BY BlockingSPID, SPID, BlockingStatement, LevelRow,  [Hostname]
ORDER BY MIN(RowNo), LevelRow
 
SELECT
    CASE
        WHEN [BlockingSPID] = 0
        THEN '
****Head of Blocking Chain SPID hostname:' + rtrim(hostname) + ' Spid:' + CAST([SPID] AS VARCHAR(5)) + '...
'
            + 'SPID ' + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
                + '
'
                + '...is blocking the following SPID(s):'
        WHEN [LevelRow] > 1
        THEN '
'
            + SPACE(LevelRow * 5) + 'SPID '
            + CAST(BlockingSPID AS VARCHAR(50))
            + ' is, in turn, blocking the following SPID:
'
            + SPACE((LevelRow + 1) * 5) + 'SPID ' + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')

        ELSE '
'
            + SPACE(LevelRow * 5) + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
                + '
'
    END
FROM @results

IF NOT EXISTS (SELECT TOP 1 * FROM @results)
    PRINT 'No Blocking Chains Were Found'

BEGIN TRY
    DROP TABLE #Processes
END TRY
BEGIN CATCH
END CATCH