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;

如下截圖所示，當死鎖出現後，那麼這個告警設置是否會發送郵件出來呢？ 答案是否定的，你可以檢查告警的歷史情況，如下所示：