分享:MSSQL找出发生死锁,阻塞的语句并直接KILL掉

最新推荐文章于 2024-04-24 13:29:56 发布

最新推荐文章于 2024-04-24 13:29:56 发布 · 252 阅读

本文介绍了一个SQL Server存储过程，用于检测并终止发生死锁或阻塞的进程。通过查询系统表来查找被阻塞的进程，并使用KILL命令来解决阻塞问题。

USE master 
GO
IF OBJECT_ID('dbo.sp_kill_blocked','P') IS NOT NULL 
DROP PROCEDURE dbo.sp_kill_blocked
GO 
CREATE PROCEDURE dbo.sp_kill_blocked 
AS 
/* 
作者:陈恩辉-弘恩
找出发生死锁,阻塞的语句并直接KILL掉
*/
DECLARE @kill VARCHAR(255) , @dbcc_inputbuffer VARCHAR(255) ;
WITH    cte
          AS ( SELECT   *
               FROM     master.sys.sysprocesses p
               WHERE    p.spid > 50
                        AND p.blocked > 0
             )
    SELECT  @kill = ' kill ' + CAST(blocked AS VARCHAR) ,
            @dbcc_inputbuffer = ' dbcc inputbuffer( ' + CAST(blocked AS VARCHAR) + ')'
    FROM    cte a
    WHERE   NOT EXISTS ( SELECT 1
                         FROM   cte b
                         WHERE  a.blocked = b.spid )
IF @kill IS NOT NULL
    OR @dbcc_inputbuffer IS NOT NULL 
    BEGIN                          
        SELECT  GETDATE() AS exec_time ,
                @kill AS [exec_kill] ,
                @dbcc_inputbuffer AS dbcc_inputbuffer ;
        PRINT @dbcc_inputbuffer 
		PRINT @kill        
        EXEC (@dbcc_inputbuffer)                        
        EXEC (@kill)
    END 
ELSE 
    BEGIN 
        SELECT  GETDATE() AS exec_time ,'没有死锁，请确认！' AS result 
    END 
GO 
 
EXEC sp_Ms_marksystemobject 'sp_kill_blocked'
GO