[SQL Server] Monitor and Alert deadlock event

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

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

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



@確認有加入xml_deadlock_report event

@create a SP for query event data and send mail

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

select top 1
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 
              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]
EXEC msdb.dbo.sp_add_alert @name=N'Deadlock Alert', 
		@performance_condition=N'Locks|Number of Deadlocks/sec|_Total|>|0', 


沒多久,我就可以收到xml formatter of deadlock report的email通知



[SQL SERVER][Maintain]監控Deadlock

[SQL SERVER][Maintain]擴充的事件(1)

[SQL SERVER][Maintain]擴充的事件(2)

Finding and Extracting deadlock information using Extended Events

Responding to extended events in near real time

Monitor deadlock by Extend Event

SQL SERVER – Introduction to Extended Events – Finding Long Running Queries

Extended Event To Track Data And Log File Size Changes

SQL Server Event Handling: Event Notifications

5 Extended Events Sessions Your SQL Server Instance Cannot Live Without

Ring Buffer target code for extended events in SQL Database

How to Query Extended Events Target XML