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.

SQL will allocate it's own deadlock victims as it sees fit (iirc it's based upon the estimated operator cost of the query). Why are you finding it necessary to do this yourself? You'd be much better off looking at Isolation Levels within SQL Server to stop the deadlocks in the first place. — Rich Benner, May 16 '16 at 11:27
When this happen all the users get error message and entries can not save. unless I do above exercises. (And now there is finalisation time and this is frequently happen) — curious K, May 16 '16 at 11:57
If you're going to the extreme of killing processes automatically then you may as well go to the extreme of switching on Read Committed Snapshot Isolation. — Rich Benner, May 16 '16 at 12:00
you could add "SET DEADLOCK_PRIORITY LOW" to the session running the query with "mytext". This way, if deadlock condition occurs, this session will be chosen as victim if deadlocked with another session with priority normal(default) or high — Bob Klimes, May 19 '16 at 20:25
Congratulations, check [dbareaction](http://dbareactions.com/post/144607242432/when-im-reading-through-stackoverflow-questions) :) — Lukasz Szozda, May 20 '16 at 12:42

score 2 · Answer 1 · answered Apr 18 '19 at 19:43

Here is much more concise query to kill all sessions running specific sql.

DECLARE @kill varchar(8000); SET @kill = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE database_id = db_id('dldb') and session_id <> @@SPID 
    and text like '%FROM dl2ResultsHeaderItemSubTree%'
EXEC(@kill);

This works since SQL Server 2005.

Merged from List the queries running on SQL Server and Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK).

gofr1 · Accepted Answer · 2016-05-16T13:17:09.890

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

edited May 16 '16 at 13:17

answered May 16 '16 at 12:31

gofr1

15,741
11
42
52

I don' want to kill all the session only specific event session who is create deadlock in my database e.g. the word 'SUBLEDGER'. If this word found in eventinfo that spid goes kill by above t-sql – curious K May 16 '16 at 13:14
Well, see the changes in my answer, I put your query instead of mine. All spid from your query in TEMP table - than there killed in dynamic sql query's in while loop. – gofr1 May 16 '16 at 13:18

score 1 · Answer 3 · answered May 20 '16 at 11:26

From above ans, I change as per my requirement.

/*drop table #inputbuffer 
 --First Time create Table 

create table #inputbuffer
(eventType varchar(255) ,
parameters int ,
procedureText varchar(255),
spid varchar(6))
*/
declare @spid varchar(6)
declare @sql varchar(50)

declare sprocket cursor fast_forward for 
select spid  from SYS.sysprocesses
where dbid =db_id() and spid <> @@SPID and blocked <>0 and lastwaittype LIKE 'LCK%'
union all 
select blocked  from SYS.sysprocesses
where dbid =db_id() and spid <> @@SPID and blocked <>0 and lastwaittype LIKE 'LCK%'

open sprocket
fetch next from sprocket into
@spid

while @@fetch_status = 0
    begin
        set @sql = 'dbcc inputbuffer(' + @spid + ')'
        insert into #inputbuffer(eventType, parameters, procedureText)
        exec (@sql)

        update #inputbuffer
            set spid = @spid
            where spid is null

        fetch next from sprocket into
        @spid
    end

close sprocket
deallocate sprocket

if @@cursor_rows <> 0
    begin 
        close sprocket
        deallocate sprocket
end

DELETE from #inputbuffer  where procedureText NOT like '%SUBLED%'

select spid, eventType, parameters, procedureText from #inputbuffer  

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

SELECT @n = COUNT(*) FROM #inputbuffer

WHILE @i < @n
BEGIN 
    SELECT TOP 1 @s = spid from #inputbuffer 
    SET @sqln = @kill + cast(@s as nvarchar(10))
    select @sqln
    EXECUTE sp_executesql @sqln
    delete from #inputbuffer where spid = @s
    SET @i = @i + 1
END

Automatically kill session of some specific task

3 Answers3

Linked