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

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

原创最新推荐文章于 2024-04-24 13:29:56 发布 · 置顶 · 2.2k 阅读

CC 4.0 BY-SA版权

本文提供了一种SQL Server中自动解除死锁和阻塞的方法，通过编写存储过程`sp_kill_blocked`，该过程可以查找并直接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