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.
5Here 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
2From 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
0ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoanBqZWeEdn%2BOmqytp52WwaqvwKWjsmWbnrmtedKeqqyhn6N6sLKMrKamnV2ovaavyJ%2BgnGWklsCs