SQL Blocking script

List SQL processes which are blocked. List which  SQL processes are blocking other processes.

/******************************************************************
-- Description  :processes which are blocked, which processes are blocking other processes
-- Last Modified:19/03/2007
******************************************************************/
SELECT * FROM dbo.sysprocesses WHERE blocked <> 0;
SELECT * FROM dbo.sysprocesses WHERE spid IN (SELECT blocked FROM dbo.sysprocesses where blocked <> 0);

How to monitor Blocked Processes with SQL Alert and email sp_whoisActive report

Read More

sys.sysprocesses and permissions

Cached blocking history with sys.dm_db_index_operational_stats

SQL Server – Deleting Distributed Orphaned Transactions