Michael J. Swart

A query that uses a CTE to determine who’s blocking who. I find it useful but a bit slow if things have gotten really really crazy.

SELECT spid, blocked INTO #tempSysProcesses FROM master..sysprocesses;

with BlockingChains (session_chains, leaf) AS

(  
 --base case  
 select distinct cast(spid as varchar(max)), spid  
 from #tempSysProcesses with (nolock)  
 where spid >= 50
    and (blocked = 0 or blocked = spid)

    UNION ALL

    --recursive step
  select bc.session_chains + ', ' + 
        cast(sp.spid as varchar(max)),
        sp.spid
    from #tempSysProcesses sp with (nolock)
    join BlockingChains bc
        on sp.blocked = bc.leaf
    where sp.blocked <> sp.spid
)
select leaf as session_id, session_chains as blocking_chain
from BlockingChains
where session_chains like '%,%'
order by leaf

DROP TABLE #tempSysProcesses