Automatically kill session of some specific task

Opinion

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.

5

3 Answers

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).

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
2

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
0

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoanBqZWeEdn%2BOmqytp52WwaqvwKWjsmWbnrmtedKeqqyhn6N6sLKMrKamnV2ovaavyJ%2BgnGWklsCs