Script to kill the top blocker and report what it was doing.

/*
        block-sniper.sql
        cjm@integer.org
        http://www.Integer.org

Function: This script identifies the blocking locks at the top of the blocking chain and kills them.
          Specifically, it reports what the top blocking spids are doing, kills them, waits three seconds and then reports on current blocking status.

Questions: See header above.
Updates: http://www.integer.org/cjm/files/block-sniper.sql

copyright (C) 2003 Cameron Michelis copying and redistribution of this file is permitted provided this notice and the above comments are preserved.           

*/
use master
go 
select getdate()
go
-- Report on blocking spids to be terminated.
select blocked as "Blocker" 
,count(*) as "Victim Count" 
from master..sysprocesses 
where blocked > 0 
group by blocked

go 
DECLARE @blocker varchar(75)
DECLARE blocker_cursor CURSOR FOR select distinct blocked from sysprocesses where blocked !=0

OPEN blocker_cursor

  FETCH NEXT FROM blocker_cursor INTO @blocker

  WHILE (@@fetch_status <> -1)

    BEGIN
      IF (@@fetch_status = -2)
        BEGIN
FETCH NEXT FROM blocker_cursor INTO @blocker
          CONTINUE
        END

-- Only kill process if it is the leader and more than 5 seconds old.
IF @blocker not in (select spid from sysprocesses where blocked != 0)
AND (select datediff(ss,last_batch,getdate()) FROM sysprocesses where blocked = @blocker) > 5 

BEGIN
print 'killing head blocker, spid #' + @blocker + ' who is blocking connections with this command: '
print ''
exec ('dbcc inputbuffer (' + @blocker + ')')
exec ('kill ' + @blocker ) -- kill
FETCH NEXT FROM blocker_cursor INTO @blocker

-- Display Remaining Blocking Spids
waitfor delay '00:00:03' --wait three seconds for the blocks to clear, then display status
print ''
print 'Remaining Blockers...'
print ''
select blocked as "Blocker" 
,count(*) as "Victim Count" 
from master..sysprocesses 
where blocked > 0 
group by blocked

END

ELSE
FETCH NEXT FROM blocker_cursor INTO @blocker
      END
DEALLOCATE blocker_cursor
print 'Process Complete...'