SQL2005 Find Blocking process

Yesterday my office practically had a meltdown because our site was being unresponsive. Of course, this happened right when I left for the gym.

8 missed calls later, someone taps me on the shoulder, while I'm on the treadmill at 24hour, and informs me that the site is down. Are you kidding me?? It was a co-worker who was at lunch also, they all know I go to gym at lunch. Anyway....

Dripping with sweat, I tear through the streets praying the cops are on their doughnut break. It appeared that they were too.

RDP'd into server, notice cpu maxed, 300+ active connections, and at the same time 20+ IM's from people in the office telling me the site does not appear to be working. 2 minutes into it, I find a crap load of processes being blocked, thank you sp_who2. Found process hanging everything up. Killed it, waited 5 more minutes for process to roll back, then proceeded on to damage control.

Enlight of this I needed to get the blocks on the radar(visible) for others to look at in case I'm ever not around, like today at lunch.

On to google...searching..read..read... found.

http://www.zdnetasia.com/techguide/database-management/0,3800010795,62038701,00.htm
Find blocking processes using recursion in SQL Server 2005

Adapted code into my own flavor, below, but mad props to this guy for laying the ground work.

CREATE Procedure [dbo].[sp_FindBlocks]
AS
BEGIN
SET NOCOUNT ON;
--//Find Blocking process
DECLARE @Processes TABLE (
[spid] [smallint] NOT NULL,
[BlockingSPID] [smallint] NOT NULL,
[DatabaseName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[program_name] [nchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[loginame] [nchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Definition] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Host] varchar(255) null
)

INSERT INTO @Processes
SELECT
s.spid
,s.blocked
,DB_NAME(s.dbid)
,s.program_name
,s.loginame
,OBJECT_NAME(objectid)
,LEFT(CAST(text AS VARCHAR(MAX)),100)
,s.hostname
FROM
sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE
s.spid > 50;

WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow, Host, LoginName, ProgramName)
AS
(
SELECT
s.SPID
, s.BlockingSPID
, s.Definition
,ROW_NUMBER() OVER(ORDER BY s.SPID)
,0 AS LevelRow
, s.host
, s.loginame
, s.program_name
FROM
@Processes s
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
, d.host
, d.LoginName
, d.ProgramName
FROM
@Processes r
JOIN Blocking d ON r.BlockingSPID = d.SPID
WHERE
r.BlockingSPID > 0
)

SELECT * FROM Blocking
ORDER BY RowNo, LevelRow
END

It's still a work in process, will repost when I get it nailed down to my liking.

TTFN