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.
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!