/*
        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...'