[聚合文章] SQL Server 死锁的告警监控

今天这篇文章总结一下如何监控 SQL Server 的死锁，其实以前写过 MS SQL 监控错误日志的告警信息 ，这篇文章着重介绍如何监控数据库的死锁，当然这篇文章不分析死锁产生的原因、以及如何解决死锁。死锁（ Dead Lock ）的错误信息在 sys.messages 中的 message_id 为 1205 ，可以使用下面 SQL 查看。

   SELECT * FROM sys . messages WHERE message_id = 1205

那么接下来，我们来设置一下死锁（ Dead Lock ）告警吧 , 如下所示，当然你可以使用 UI 界面设置。

 
USE [msdb]
GO
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE NAME='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
IF EXISTS(SELECT 1 FROM msdb.dbo.sysalerts WHERE name='SQL Server Dead Lock Detected')
BEGIN
    EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Dead Lock Detected';
END
GO
 
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysalerts WHERE name='SQL Server Dead Lock Detected')
BEGIN
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Dead Lock Detected', 
        @message_id=1205, 
        @severity=0, 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=1, 
        @category_name=N'DBA_MONITORING', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
END
GO
 
IF NOT EXISTS ( SELECT  *
                FROM    msdb.dbo.sysnotifications
                WHERE   alert_id = ( SELECT id
                                     FROM   msdb.dbo.sysalerts
                                     WHERE  name = 'SQL Server Dead Lock Detected'
                                   ) )
    BEGIN
 
        EXEC msdb.dbo.sp_add_notification @alert_name = N'SQL Server Dead Lock Detected',
            @operator_name = N'YourSQLDba_Operator', @notification_method = 1;
    END;
GO

执行上面脚本后，就会在 SQL Server 的告警里面新增一个名为 SQL Server Dead Lock Detected' 的告警，那么现在是否 OK 了呢？当然不是，我们来测试验证一下吧，首先准备测试的表和数据。

USE YourSQLDba ;

GO

CREATE TABLE DEADLOCK1 ( ID INT DEFAULT ( 0 ));

CREATE TABLE DEADLOCK2 ( ID INT DEFAULT ( 0 ));

INSERT INTO DEADLOCK1 VALUES ( 1 );

INSERT INTO DEADLOCK2 VALUES ( 1 );

GO

如下所示，在两个会话窗口执行下面脚本，构造死锁出现的场景。

-- 会话窗口 1 执行下面 SQL

BEGIN TRAN

  UPDATE DEADLOCK1 SET ID = ID + 1 ;

  WAITFOR DELAY '00:00:20' ;

  SELECT * FROM DEADLOCK2

ROLLBACK TRAN ;

EXEC master .. sp_altermessage 1205 , 'WITH_LOG' , TRUE ;

GO

-- 会话创建 2 执行下面 SQL

BEGIN TRAN

  UPDATE DEADLOCK2 SET ID = ID + 1 ;

  WAITFOR DELAY '00:00:20' ;

  SELECT * FROM DEADLOCK1

ROLLBACK TRAN ;

如下截图所示，当死锁出现后，那么这个告警设置是否会发送邮件出来呢？ 答案是否定的，你可以检查告警的历史情况，如下所示：

从 History 界面，我们可以看到这个告警没有被触发，那么这个是什么原因呢？原因其实很简单，因为 message_id 为 1205 的消息字段 is_event_logged 默认是 0 ，这意味着出现错误消息将不会记入事件日志。我们可以使用小SQL将其值设置为 1

EXEC master .. sp_altermessage 1205 , 'WITH_LOG' , TRUE ;

GO

执行上面脚本后， message_id 为 1205 的记录的 is_event_logged 字段值将被设置为 1 ，当数据库出现死锁时，就会被记录到错误日志，当然这个只是简单消息的记录，如果你要跟踪、解决死锁问题，就需要记录死锁的详细信息，需要在服务端针对所有的 Session 开启 Trace flag 1222 。

DBCC TRACEON(1222,-1);