Automatically kill session of some specific task

Question

In some report generation we are facing deadlock problem in SQL Server, so what I can do is

select * 
from sys.sysprocesses 
where dbid = db_id() 
  and spid <> @@SPID 
  and blocked <> 0  
  and lastwaittype LIKE 'LCK%'

dbcc inputbuffer (SPID from above query result)

dbcc inputbuffer (blocked from above query result)

If EventInfo column contains 'mytext', I want to terminate that section by

Kill 53  

53 is no of SPID or blocked where I see specific text whose connection I want to kill

I want to automate this process whenever deadlock create and the specific word is found kill those session. without users interval or action.

Answer 1

Sometimes I use this old query to get rid of sessions with specific description:

declare @t table (sesid int)

--Here we put all sessionid's with specific description into temp table
insert into @t 
select spid
from sys.sysprocesses 
where dbid = db_id() 
  and spid <> @@SPID 
  and blocked <> 0  
  and lastwaittype LIKE 'LCK%'

DECLARE @n int,
        @i int= 0,
        @s int,
        @kill nvarchar(20)= 'kill ',
        @sql nvarchar (255)

SELECT @n = COUNT(*) FROM @t
--Here we execute `kill` for every sessionid from @t in while loop
WHILE @i < @n
BEGIN 
    SELECT TOP 1 @s = sesid from @t
    SET @sql = @kill + cast(@s as nvarchar(10))

    --select @sql
    EXECUTE sp_executesql @sql

    delete from @t where sesid = @s
    SET @i = @i + 1
END