Capturing and decrypting Deadlock information from SQL Server Part 1

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.