SQL Server: Capture database deadlock by xevent & job alerts and send email to you !

---

1.Introduction

This article attempts at explaining how to capture Database deadlock in SQL Server. And this system send email to you!

2.Check you environment

All this page scripts can only execute in SQL Server version 2012,2014,2016.

--check sql server version
select @@version
--check sql server job is running
deCLARE @agent NVARCHAR(512);
SELECT @agent = COALESCE(N'SQLAgent$' + CONVERT(SYSNAME, SERVERPROPERTY('InstanceName')), N'SQLServerAgent');
EXEC master.dbo.xp_servicecontrol 'QueryState', @agent;

3.Create deadlock store table & procedure

1.Where is the store the deadlock information? 

USE [Your_Database]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE  [dbo].[DBA_LOCK_DEADLOCK_REPORT](
[no] [int] IDENTITY(1,1) NOT  NULL,
[deadlock_timeout] [datetime] NULL,
[deadlock1_id] [nvarchar](100) NULL,
[deadlock1_duration] [float] NULL,
[deadlock1_transactionname] [nvarchar](100) NULL,
[deadlock1_locktype] [nvarchar](50) NULL,
[deadlock1_clientapp] [nvarchar](200) NULL,
[deadlock1_hostname] [nvarchar](50) NULL,
[deadlock1_loginname] [nvarchar](50) NULL,
[deadlock1_query] [nvarchar](max) NULL,
[deadlock2_id] [nvarchar](100) NULL,
[deadlock2_duration] [float] NULL,
[deadlock2_transactionname] [nvarchar](100) NULL,
[deadlock2_locktype] [nvarchar](50) NULL,
[deadlock2_clientapp] [nvarchar](200) NULL,
[deadlock2_hostname] [nvarchar](50) NULL,
[deadlock2_loginname] [nvarchar](50) NULL,
[deadlock2_query] [nvarchar](max) NULL,
PRIMARY KEY  CLUSTERED 
(
[no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON  [PRIMARY]
) ON  [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
 
CREATE NONCLUSTERED INDEX [IX_DBA_DBA_LOCK_DEADLOCK_REPORT_deadlocktimeout]  ON  [dbo].[DBA_LOCK_DEADLOCK_REPORT]
(
[deadlock_timeout] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON  [PRIMARY]
GO

  

2.This is converting procedure from xml to sql server data.

USE [Your_Database]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE proc [dbo].[spDeadLockReport]
as
begin
DECLARE @mStartDate DATETIME;
SET @mStartDate=(SELECT isnull(MAX([deadlock_timeout]),getdate()-1) FROM  ADMIN..DBA_LOCK_DEADLOCK_REPORT)
 
INSERT INTO  ADMIN.dbo.DBA_LOCK_DEADLOCK_REPORT([deadlock_timeout], [deadlock1_id], [deadlock1_duration], [deadlock1_transactionname], [deadlock1_locktype], [deadlock1_clientapp], [deadlock1_hostname], [deadlock1_loginname], [deadlock1_query], [deadlock2_id], [deadlock2_duration], [deadlock2_transactionname], [deadlock2_locktype], [deadlock2_clientapp], [deadlock2_hostname], [deadlock2_loginname], [deadlock2_query])
SELECT x.y.value('(@timestamp)[1]', 'datetime')                                                        '[deadlock_timeout]',
       x.y.value('(./data/value/deadlock/process-list/process/@id)[1]', 'NVARCHAR(100)')               '[deadlock1_id]',
       x.y.value('(./data/value/deadlock/process-list/process/@waittime)[1]', 'float') / 1000          '[deadlock1_duration]',
       x.y.value('(./data/value/deadlock/process-list/process/@transactionname)[1]', N'NVARCHAR(100)') AS  '[deadlock1_transactionname]',
       x.y.value('(./data/value/deadlock/process-list/process/@lockMode)[1]', N'NVARCHAR(50)')         AS  '[deadlock1_locktype]',
       x.y.value('(./data/value/deadlock/process-list/process/@clientapp)[1]', N'NVARCHAR(200)')       AS  '[deadlock1_clientapp]',
       x.y.value('(./data/value/deadlock/process-list/process/@hostname)[1]', N'NVARCHAR(50)')         AS  '[deadlock1_hostname]',
       x.y.value('(./data/value/deadlock/process-list/process/@loginname)[1]', N'NVARCHAR(50)')        AS  '[deadlock1_loginname]',
       x.y.value('(./data/value/deadlock/process-list/process/inputbuf)[1]', N'NVARCHAR(max)')         AS  '[deadlock1_query]',
       x.y.value('(./data/value/deadlock/process-list/process/@id)[2]', 'NVARCHAR(100)')               '[deadlock2_id]',
       x.y.value('(./data/value/deadlock/process-list/process/@waittime)[2]', 'float') / 1000          '[deadlock2_duration]',
       x.y.value('(./data/value/deadlock/process-list/process/@transactionname)[2]', N'NVARCHAR(100)') AS  '[deadlock2_transactionname]',
       x.y.value('(./data/value/deadlock/process-list/process/@lockMode)[2]', N'NVARCHAR(50)')         AS  '[deadlock2_locktype]',
       x.y.value('(./data/value/deadlock/process-list/process/@clientapp)[2]', N'NVARCHAR(200)')       AS  '[deadlock2_clientapp]',
       x.y.value('(./data/value/deadlock/process-list/process/@hostname)[2]', N'NVARCHAR(50)')         AS  '[deadlock2_hostname]',
       x.y.value('(./data/value/deadlock/process-list/process/@loginname)[2]', N'NVARCHAR(50)')        AS  '[deadlock2_loginname]',
       x.y.value('(./data/value/deadlock/process-list/process/inputbuf)[2]', N'NVARCHAR(max)')         AS  '[deadlock2_query]'
FROM   (SELECT Cast([target_data] AS  XML) [target_data]
        FROM   sys.dm_xe_session_targets AS st
               INNER JOIN sys.dm_xe_sessions AS  s
                       ON s.[address] = st.[event_session_address]
        WHERE  s.[name] = 'DeadlockReport') AS  [deadlock]
       CROSS APPLY [target_data].nodes('/RingBufferTarget/event') AS  x(y)
WHERE  x.y.query('.').exist('/event[@timestamp > sql:variable("@mStartDate") and @name="xml_deadlock_report"]') = 1 
end
 
 
GO

4.How to capture deadlock by xevent?

This is xevent create script.
This xevent is running on memory. Storing xevent memory is easy to lost the data.
But, We can solve this problem. Let's see next article.

--CREATE DEADLOCK XEVENT
CREATE EVENT SESSION [DeadlockReport] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report 
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
 
--XEVENT START
ALTER EVENT SESSION [DeadlockReport] ON SERVER
STATE = START
GO

5.Create your own job and job alerts to capture the deadlock

USE [msdb]
GO
 
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
 
IF NOT EXISTS (SELECT name  FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO  QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'[DBA]deadlock', 
@enabled=1, 
@notify_level_eventlog=2, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'no description', 
@category_name=N'[Uncategorized (Local)]', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO  QuitWithRollback
 
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'deadlock', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'EXEC Your_Database.dbo.spDeadLockReport', 
@database_name=N'Your_Database', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO  QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO  QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO  QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK  TRANSACTION
EndSave:
 
GO

And add job alerts.

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'[DBA]Deadlock Alerts', 
@message_id=0, 
@severity=0, 
@enabled=1, 
@delay_between_responses=0, 
@include_event_description_in=0, 
@category_name=N'[Uncategorized]', 
@performance_condition=N'Locks|Number of Deadlocks/sec|_Total|>|0'
GO

6.configure the sql server agent email.

Please see below link.

link

This is sending email script.

DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
 
SET @subject = 'dba_report'
 
SET @tableHTML = 
N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: left;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;
border-left: 1px solid #aabcfe;
border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd) { background-color:#eee; }
tr:nth-child(even) { background-color:#fff; } 
</style>'+ 
N'<H3><font color="Red">SQL Deadlock</H3>' +
N'<table id="box-table" >' +
N'<tr><font color="Red">
<th>Servername</th>
<th>[deadlock_timeout]</th>
<th>[deadlock1_hostname]</th>
<th>[deadlock1_loginname]</th>
<th>[deadlock1_query]</th>
<th>[daedlock2_hostname]</th>
<th>[deadlock2_loginname]</th>
<th>[deadlock2_query]</th>
</tr>' +
CAST ( ( 
 
SELECT td = [Servername],'',
td = [deadlock_timeout],'',
td = [deadlock1_hostname] ,'',
td = [deadlock1_loginname] ,'',
td = [deadlock1_query] ,'',
td = [daedlock2_hostname] ,'',
td = [deadlock2_loginname] ,'',
td = [deadlock2_query] ,''
FROM dba_report.dbo.deadlockreport
FOR XML PATH('tr'), TYPE 
) AS  NVARCHAR(MAX) ) +
N'</table>'
 
EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'dba_report',
@recipients='your_email@your_domain.com',
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML' ;

this deadlockreport table is come from DBA_LOCK_DEADLOCK_REPORT.
Please change table column  properly.

Thank you!

---