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

最新推荐文章于 2025-02-07 14:28:35 发布

最新推荐文章于 2025-02-07 14:28:35 发布 · 517 阅读

文章标签：

本文介绍了一个SQL Server过程，用于自动检测和处理数据库中的死锁和阻塞情况。通过执行存储过程，可以快速找到并终止导致阻塞的会话。

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