Capturing and decoding Deadlock information from SQL Server Part 1
We will have two articles in this series.
1. Different ways to capture deadlock
2. Decoding deadlock data from captured deadlock data
I know this is very common issue which DBA might not be able
to completely resolve but DBA can provide detailed information regarding
Deadlock.
Let us see what are the different ways we can capture deadlock
data from SQL Server.
From SQL Server 2012 onwards we can capture deadlock data
directly from System Health Extended event session, for this we don’t need
enable any trace flags.
Earlier to SQL Server 2012, we need to enable flags 1204 and
1222 to get detailed deadlock information.
You can check my blog
to find how to enable trace flags.
Below is the script to capture deadlock, This code is robust
enough to check for SQL Server version, to get data from System health Event
session or from SQL Server error log. If it does not find any information it
will give suggestions as well.
SET NOCOUNT ON
DECLARE @stDate AS DATETIME = '2016-06-13 04:49:26' --Please key in
the UTC time at which alert created
DECLARE @DecelockText AS VARCHAR (100) = 'Process ID 113' --Replace 113 with the process ID part of deadlock
DECLARE @endDate AS DATETIME
SELECT @stDate =
DATEADD(MILLISECOND,DATEDIFF(MILLISECOND,getutcdate(),GETDATE()),@stDate)
SET @endDate = DATEADD(MINUTE, 1, @stDate)
SET @stDate =
DATEADD(MINUTE, -2, @stDate)
IF (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS
nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS
nvarchar(max))) - 1) + '.' +
REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),
LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) -
CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','')
AS numeric(18,10)) ) >= 11
BEGIN
DECLARE
@PATH VARCHAR(500)
SELECT @PATH= [path] FROM
sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION
(RECOMPILE);
SET
@PATH = @PATH + 'system_health*.xel'
CREATE TABLE
#Deadloc_Trace (DLGraph XML, Execution_time datetime)
INSERT
INTO #Deadloc_Trace
SELECT
CONVERT(xml, event_data).query('/event/data/value/child::*'),
CONVERT(xml,
event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime')
as Execution_Time
FROM
sys.fn_xe_file_target_read_file(@PATH, null, null, null)
WHERE
object_name like 'xml_deadlock_report'
SELECT
* FROM #Deadloc_Trace WHERE
Execution_Time BETWEEN @stDate and @endDate
DROP
TABLE #Deadloc_Trace
END
ELSE
BEGIN
CREATE
TABLE #TraceStatus(
TraceFlag
varchar(100)
,
[Status] int
,
[Global] int
, [Session] int)
insert
#TraceStatus(TraceFlag, [Status], [Global], [Session])
EXEC('DBCC
TRACESTATUS(1204,1222) WITH NO_INFOMSGS;')
IF
EXISTS (select * from #TraceStatus where Global > 0)
BEGIN
CREATE
TABLE #ErrLog(LogDate datetime, ProcessInfo sysname, LogText nvarchar(1000));
INSERT
INTO #ErrLog
EXEC
xp_readerrorlog 0, 1, @DecelockText;
SELECT
TOP 1 @stDate = LogDate
FROM
#ErrLog
ORDER
BY LogDate DESC;
SET
@endDate = DATEADD(minute,2,@stDate)
--select
@stDate, @endDate
EXEC
master.dbo.xp_readerrorlog 0, 1, null, null,@stDate, @endDate
DROP
TABLE #ErrLog;
END
ELSE
BEGIN
PRINT
'Currently deadlock traces are not enabled and hence we cannot gather more
information.
We need
to enable Deadlock traces 1204,1222 to capture deadlock information in future.
'
END
DROP
TABLE #TraceStatus;
END
Hope this helps someone thanks for checking in.
