[SQL Server] Monitor and Alert deadlock event

2018-04-24

1719
0
SQL_SERVER

SQL Server 2012預設已經使用Extended Event來監控deadlock，這篇來看看如何實現Alert

Extended Event是SQL Server主推的診斷工具(追蹤和監控)，

針對Extended Event我寫過兩篇文章，這裡就不在多做說明，

這篇主要加上notifications，下面自己做個紀錄。

@確認有加入xml_deadlock_report event

@create a SP for query event data and send mail

create proc dba_ProcessDeadlockGraphs
as
set nocount on;
DECLARE @deadlock XML;
DECLARE @email_message nvarchar(MAX);

select top 1
    @deadlock=DeadlockGraph
from (
SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
, XEvent.value('(/event/@timestamp)[1]','DATETIMEOFFSET') AS BeginTime
    FROM ( SELECT XEvent.query('.') AS XEvent
        FROM ( SELECT CAST(target_data AS XML) AS TargetData
            FROM sys.dm_xe_session_targets st
                JOIN sys.dm_xe_sessions s
                ON s.address = st.event_session_address
            WHERE s.name = 'system_health'
                AND st.target_name = 'ring_buffer' 
              ) AS Data 
              CROSS APPLY 
                 TargetData.nodes 
                    ('RingBufferTarget/event[@name="xml_deadlock_report"]')
              AS XEventData ( XEvent ) 
      ) AS src
) result
order by BeginTime desc

SELECT @email_message = CONVERT(nvarchar(max), @deadlock)
EXEC msdb.dbo.sp_send_dbmail
             @profile_name = 'ricogoogle', -- your defined email profile 
             @recipients = 'abc@gmail.com', -- your email
             @subject = 'Deadlock Notification',
             @body = @email_message;

@create a agent job and Alert

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Deadlock Alert', 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@performance_condition=N'Locks|Number of Deadlocks/sec|_Total|>|0', 
		@job_id=N'3e10aa5a-89cb-4c42-bea7-21e35a5d836c'
GO