原文: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);

?

?

?

總結

以上是生活随笔為你收集整理的SQL Server 死锁的告警监控的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。