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