sqlserver 删除造成锁表的sleeping进程脚本

柔光的暖阳◎ 2023-07-11 15:07 10阅读 0赞

create  PROCEDURE dbo.Kill\_\_Sleeping\_Processes  
AS  
BEGIN  
declare @v\_sid int  
declare @sql varchar(50)  
declare cur\_kill\_sleep cursor for   
WITH sess AS  
(  
    SELECT  
        blocking\_session\_id,          
        es.session\_id,  
        er.status,  
        individual\_query = SUBSTRING (qt.text, (er.statement\_start\_offset/2)+1,  ((CASE WHEN er.statement\_end\_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))  \* 2 ELSE er.statement\_end\_offset END - er.statement\_start\_offset)/2)+1)  
    FROM  
        sys.dm\_exec\_requests er  
        INNER JOIN sys.dm\_exec\_sessions es ON er.session\_id = es.session\_id  
        CROSS APPLY sys.dm\_exec\_sql\_text(er.sql\_handle)as qt  
    WHERE  
        es.session\_id > 50  
        AND es.session\_Id NOT IN (@@SPID)  
              AND qt.text <> 'sp\_server\_diagnostics'  
)  
SELECT  
    session\_id  
FROM  
    sess WHERE individual\_query NOT LIKE 'waitfor%' and status='sleeping'  
UNION ALL   
  select  es.session\_id  
FROM  
    sys.dm\_exec\_sessions es  
    INNER JOIN sys.dm\_exec\_connections ec ON es.session\_id = ec.session\_id  
    CROSS APPLY sys.dm\_exec\_sql\_text(ec.most\_recent\_sql\_handle)as qt  
WHERE  status='sleeping'  and  
    ec.most\_recent\_session\_id IN  
    (  
        SELECT blocking\_session\_id FROM sess WHERE blocking\_session\_id NOT  IN(SELECT DISTINCT session\_id FROM sess)  
    ) AND  qt.text NOT in ('waitfor delay @waittime    ','sp\_server\_diagnostics')

open cur\_kill\_sleep  
fetch next from cur\_kill\_sleep into @v\_sid  
while @@fetch\_status=0  
begin  
SET @sql = 'KILL ' + RTRIM(@v\_sid)  
EXEC ( @sql )  
print '删除'+CONVERT(varchar(10),@v\_sid)  
fetch next from cur\_kill\_sleep into @v\_sid  
end  
close cur\_kill\_sleep  
deallocate cur\_kill\_sleep  
END