/*******************************************************************\
*               Copyright © Pro-DBA.com 2006, all rights reserved   *
*                                                                   *
* Name     :    ShowBlockingProcesses.sql                           *
*                                                                   *
* Purpose  :    Lists the number of blocked processes and time      *
*               blocked for each blocking process                   *
*                                                                   *
* Tested on:    SQL Server 7.0, SQL Server 2000, SQL Server 2005    *
*                                                                   *
*                                                                   *
\*******************************************************************/

SET NOCOUNT ON

SELECT a.spid as "Block", (SELECT COUNT(*) FROM master.dbo.sysprocesses b WHERE b.blocked = a.spid) AS "BlockCount", 
(SELECT MAX(waittime) FROM master.dbo.sysprocesses b WHERE b.blocked = a.spid) AS "BlockTime", 
a.status, a.program_name, a.cmd, a.last_batch  FROM master.dbo.sysprocesses a
WHERE a.spid in (SELECT blocked FROM master.dbo.sysprocesses WHERE blocked <> 0)
ORDER BY a.blocked ASC, BlockCount DESCes DESC