自动终止某些特定任务的会话 [英] Automatically kill session of some specific task

问题描述

在某些报表生成中，我们面临SQL Server中的死锁问题，所以我能做的就是

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)

如果 EventInfo 列包含 mytext，我想终止该部分由

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

Kill 53  

53 没有SPID或被阻止，在我看到要杀死其连接的特定文本的地方

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.

推荐答案

有时候，我使用这个旧查询来摆脱具有特定描述的会话：

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

这篇关于自动终止某些特定任务的会话的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！