This article attempts at explaining how to capture Database deadlock in SQL Server. And this system send email to you!
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;
1.Where is the store the deadlock information?
USE [Your_Database]
GO
SET
ANSI_NULLS
ON
QUOTED_IDENTIFIER
CREATE
TABLE
[dbo].[DBA_LOCK_DEADLOCK_REPORT](
[
no
] [
int
] IDENTITY(1,1)
NOT
NULL
,
[deadlock_timeout] [datetime]
[deadlock1_id] [nvarchar](100)
[deadlock1_duration] [
float
]
[deadlock1_transactionname] [nvarchar](100)
[deadlock1_locktype] [nvarchar](50)
[deadlock1_clientapp] [nvarchar](200)
[deadlock1_hostname] [nvarchar](50)
[deadlock1_loginname] [nvarchar](50)
[deadlock1_query] [nvarchar](
max
)
[deadlock2_id] [nvarchar](100)
[deadlock2_duration] [
[deadlock2_transactionname] [nvarchar](100)
[deadlock2_locktype] [nvarchar](50)
[deadlock2_clientapp] [nvarchar](200)
[deadlock2_hostname] [nvarchar](50)
[deadlock2_loginname] [nvarchar](50)
[deadlock2_query] [nvarchar](
PRIMARY
KEY
CLUSTERED
(
ASC
WITH
(PAD_INDEX =
OFF
, STATISTICS_NORECOMPUTE =
, IGNORE_DUP_KEY =
, ALLOW_ROW_LOCKS =
, ALLOW_PAGE_LOCKS =
] TEXTIMAGE_ON [
NONCLUSTERED
INDEX
[IX_DBA_DBA_LOCK_DEADLOCK_REPORT_deadlocktimeout]
[dbo].[DBA_LOCK_DEADLOCK_REPORT]
[deadlock_timeout]
, SORT_IN_TEMPDB =
, DROP_EXISTING =
, ONLINE =
2.This is converting procedure from xml to sql server data.
proc [dbo].[spDeadLockReport]
as
begin
DECLARE
@mStartDate DATETIME;
@mStartDate=(
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])
x.y.value(
'(@timestamp)[1]'
'datetime'
'[deadlock_timeout]'
'(./data/value/deadlock/process-list/process/@id)[1]'
'NVARCHAR(100)'
'[deadlock1_id]'
'(./data/value/deadlock/process-list/process/@waittime)[1]'
'float'
) / 1000
'[deadlock1_duration]'
'(./data/value/deadlock/process-list/process/@transactionname)[1]'
, N
AS
'[deadlock1_transactionname]'
'(./data/value/deadlock/process-list/process/@lockMode)[1]'
'NVARCHAR(50)'
'[deadlock1_locktype]'
'(./data/value/deadlock/process-list/process/@clientapp)[1]'
'NVARCHAR(200)'
'[deadlock1_clientapp]'
'(./data/value/deadlock/process-list/process/@hostname)[1]'
'[deadlock1_hostname]'
'(./data/value/deadlock/process-list/process/@loginname)[1]'
'[deadlock1_loginname]'
'(./data/value/deadlock/process-list/process/inputbuf)[1]'
'NVARCHAR(max)'
'[deadlock1_query]'
'(./data/value/deadlock/process-list/process/@id)[2]'
'[deadlock2_id]'
'(./data/value/deadlock/process-list/process/@waittime)[2]'
'[deadlock2_duration]'
'(./data/value/deadlock/process-list/process/@transactionname)[2]'
'[deadlock2_transactionname]'
'(./data/value/deadlock/process-list/process/@lockMode)[2]'
'[deadlock2_locktype]'
'(./data/value/deadlock/process-list/process/@clientapp)[2]'
'[deadlock2_clientapp]'
'(./data/value/deadlock/process-list/process/@hostname)[2]'
'[deadlock2_hostname]'
'(./data/value/deadlock/process-list/process/@loginname)[2]'
'[deadlock2_loginname]'
'(./data/value/deadlock/process-list/process/inputbuf)[2]'
'[deadlock2_query]'
Cast
([target_data]
XML) [target_data]
sys.dm_xe_session_targets
st
INNER
JOIN
sys.dm_xe_sessions
s
s.[address] = st.[event_session_address]
WHERE
s.[
name
] =
'DeadlockReport'
[deadlock]
CROSS
APPLY [target_data].nodes(
'/RingBufferTarget/event'
x(y)
x.y.query(
'.'
).exist(
'/event[@timestamp > sql:variable("@mStartDate") and @name="xml_deadlock_report"]'
) = 1
end
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
EVENT SESSION [DeadlockReport]
SERVER
ADD
EVENT sqlserver.xml_deadlock_report
TARGET package0.ring_buffer
(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=
,STARTUP_STATE=
--XEVENT START
ALTER
STATE = START
USE [msdb]
BEGIN
TRANSACTION
@ReturnCode
INT
@ReturnCode = 0
IF
EXISTS (
msdb.dbo.syscategories
=N
'[Uncategorized (Local)]'
AND
category_class=1)
@ReturnCode = msdb.dbo.sp_add_category @class=N
'JOB'
, @type=N
'LOCAL'
, @
IF (@@ERROR <> 0
OR
@ReturnCode <> 0)
GOTO
QuitWithRollback
END
@jobId
BINARY
(16)
@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
@owner_login_name=N
'sa'
, @job_id = @jobId
OUTPUT
@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
@ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
@ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N
'(local)'
COMMIT
EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0)
ROLLBACK
EndSave:
msdb.dbo.sp_add_alert @
'[DBA]Deadlock Alerts'
@message_id=0,
@severity=0,
@delay_between_responses=0,
@include_event_description_in=0,
'[Uncategorized]'
@performance_condition=N
'Locks|Number of Deadlocks/sec|_Total|>|0'
@bodyMsg nvarchar(
@subject nvarchar(
@tableHTML nvarchar(
@subject =
'dba_report'
@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>'
'<H3><font color="Red">SQL Deadlock</H3>'
'<table id="box-table" >'
'<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
( (
td = [Servername],
''
td = [deadlock_timeout],
td = [deadlock1_hostname] ,
td = [deadlock1_loginname] ,
td = [deadlock1_query] ,
td = [daedlock2_hostname] ,
td = [deadlock2_loginname] ,
td = [deadlock2_query] ,
dba_report.dbo.deadlockreport
FOR
XML PATH(
'tr'
), TYPE
NVARCHAR(
) ) +
'</table>'
msdb.dbo.sp_send_dbmail
@profile_name =
@recipients=
'your_email@your_domain.com'
@subject = @subject,
@body = @tableHTML,
@body_format =
'HTML'
;