今天這篇文章總結一下如何監控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' ;?
ENDGO?
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';
ENDGO?
?
IF?NOT?EXISTS(SELECT 1 FROM msdb.dbo.sysalerts WHERE name='SQL Server Dead Lock Detected')
BEGINEXEC 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'ENDGO?
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);
?
?
?
的使用)
