[Sql Server] 當 SQL Server 發生 Deadlock 時,自動發送 Email

同事分享一篇文章,關於資料庫發生DeadLock時,如何通知管理者。不過內容很簡略,摸了一陣子才搞起來。

Step by Step


1.啟用目標資料庫(本例使用AdventureWorks2014)與msdb的Service Broker。

執行語法前需先將SQL Server Agent停用,等執行後再打開

2.建立DeadlockEvents資料表、排程及通知

此部份引用msdn範例

3.設定DatabaeMail

設定SQL Server Agent需使用哪一個Mail Profile發送
image
設定SMTP及發送者的Mail資訊
image
image

4.SQL Server Agent套用設定

image

5.在Alerts 事件加入Mail通知

此事件是在第2個步驟的Script所建立,作用為使用WMI Query來查詢,當DeadLock的錯誤發生時,會觸發此事件並執行"Capture Deadlock Graph" job來寫入一筆資料到DeadlockEvents。
image
加入要通知mail的人員
image

測試範例

開啟2個[SQL 查詢]Tab

第一個tab 執行如下
BEGIN TRANSACTION ;
GO
SELECT TOP(1) Name FROM Production.Product WITH (XLOCK) ;
GO

第二個tab
BEGIN TRANSACTION ;
GO
SELECT TOP(1) Name FROM Production.Location WITH (XLOCK) ;
GO
SELECT TOP(1) Name FROM Production.Product WITH (XLOCK) ;
GO

此時查詢會等待第一個tab。

再回到第一個tab 對Location作查詢,如下
SELECT TOP(1) Name FROM Production.Location WITH (XLOCK) ;
GO

等個約5秒會出現deadlock錯誤
SNAGHTML965402

查詢資料表,成功的話會寫入一筆資料,並發出mail。ps:預設排程時間是15秒
image

image


參考來源

https://byronhu.wordpress.com/2015/08/27/%E7%95%B6-sql-server-%E7%99%BC%E7%94%9F-deadlock-%E6%99%82%EF%BC%8C%E8%87%AA%E5%8B%95%E7%99%BC%E9%80%81-email/

https://technet.microsoft.com/library/ms186385.aspx

http://www.sanssql.com/2011/06/unable-to-start-mail-session-reason-no.html

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/742aa595-45f0-41d9-9fd0-8be9a85c8903/service-broker-is-disabled-in-msdb-or-msdb-failed-to-start?forum=sqlservicebroker

這個網誌中的熱門文章

IIS 設定只允許特定IP進入

[Sql Server] 資料庫備份筆記