Slightly fancier sp_who showing blocking transactions

sp_who_blockers.sql

DECLARE @who TABLE (SPID INT, Status VARCHAR(255), Login VARCHAR(255), HostName VARCHAR(255), BlkBy VARCHAR(255), DBName VARCHAR(255), Command VARCHAR(255), CPUTime INT, DiskIO INT, LastBatch VARCHAR(255), ProgramName VARCHAR(255), SPID2 INT, REQUESTID INT)
INSERT INTO @who EXEC sp_who2
UPDATE @who SET BlkBy = 0 WHERE BlkBy = '  .'

DECLARE @blockers TABLE (SPID INT, blocking INT)
INSERT INTO @blockers SELECT BlkBy, COUNT(*) FROM @who WHERE BlkBy > 0 GROUP BY BlkBy


-- Ultimate Block
-- If this actually returns 1000 rows, there's a circular block
SELECT      TOP 1000 Blocked.SPID, Blocked.Login, Blocked.HostName, Blocked.DBName, Blocked.ProgramName, '------Blocked By---->', TermBlock.*
FROM        @who AS Blocked
LEFT JOIN   @who AS Blocker1 ON (Blocked.BlkBy = Blocker1.SPID)
LEFT JOIN   @who AS Blocker2 ON (Blocker1.BlkBy = Blocker2.SPID AND Blocker2.SPID <> Blocked.BlkBy)
LEFT JOIN   @who AS Blocker3 ON (Blocker2.BlkBy = Blocker3.SPID AND Blocker3.SPID <> Blocker2.BlkBy AND Blocker3.SPID <> Blocked.BlkBy)
LEFT JOIN   @who AS TermBlock ON (TermBlock.SPID = COALESCE(Blocker3.SPID, Blocker2.SPID, Blocker1.SPID))
WHERE       Blocked.DBName <> 'master' 
AND         Blocked.blkby <> 0
ORDER BY    Blocked.SPID, Blocked.DBName ASC

-- Blocking SPIDs
SELECT * FROM @blockers
SELECT * FROM @who WHERE SPID IN (SELECT SPID FROM @blockers)