Identify Deadlocks in SQL Server Using Trace Flag 1222 and 1204

Introduction

This article outlines the steps which one can follow to enable Trace Flag 1222 on SQL Server to capture deadlock information. You can also use Trace Flag 1204 in conjunction with Trace Flag 1222.

Difference between Trace Flag 1222 and 1204

Explanation of Trace Flag 1222 and 1204 as per TechNet Article Detecting and Ending Deadlocks is mentioned below.

Trace Flag 1204:- Focused on the nodes involved in the deadlock. Each node has a dedicated section, and the final section describes the deadlock victim.

Trace Flag 1222:- Returns information in an XML-like format that does not conform to an XML Schema Definition (XSD) schema. The format has three major sections. The first section declares the deadlock victim. The second section describes each process involved in the deadlock. The third section describes the resources that are synonymous with nodes in trace flag 1204.

Scope of a Trace Flag can be either set to Global or Session Only. However, 1204 & 1222 trace flags can be set Global Only.

Sample Deadlock Error Message in SQL Server

Msg 1205, Level 13, State 51, Line 8
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Permissions required to enable or disable trace flags in SQL Server

You must be a membership of sysadmin fixed server role in SQL Server if you want to enable or disable.

Error Message when a uses doesn’t have permission to enable or disable trace flags

Msg 2571, Level 14, State 3, Line 1
User 'guest' does not have permission to run DBCC TRACEON.

Different ways to enable Trace Flags in SQL Server

• Enable Trace Flags in SQL Server Using DBCC Command
• Enable Trace Flags in SQL Server Using Startup Parameters

Enable Trace Flags in SQL Server Using DBCC Command

You can execute the below TSQL Command to enable Trace Flag 1204 and 1222 at global level.

/* Enable Trace Flags 1204 and 1222 at global level */

DBCC TRACEON (1204,-1)
GO
DBCC TRACEON (1222,-1)
GO

/* Second Option Enabling Trace Flags 1204 and 1222 using DBCC TRACEON Statement at global level */

DBCC TRACEON (1204, 1222, -1)
GO

However, once you restart SQL Server the trace flag is no longer available. If you need the trace flag to be available after the reboot then you will have to set it as a Startup parameter.

Enable Trace Flags in SQL Server Using Startup Parameters

Specify the Trace Flag –T1204 and –T1222 one by one as shown in the snippet below and then click Add button to add the parameter and then click OK to save the changes.

You will receive a warning message which explains that the changes will not take effect until the service is stopped and restarted.

Identify all Trace Flags which are Currently Enabled Globally in SQL Server

You can execute the below mentioned TSQL command to displays the status of all trace flags that are currently enabled globally on the SQL Server Instance.

DBCC TRACESTATUS(-1)
GO

Now that the trace flags to capture the deadlock is configured successfully.

Whenever a deadlock occurs it will capture the output similar to the one as shown in the below snippet.

This is the simplest way to capture deadlock information in SQL Server. If you need a Graphical Representation of similar information then read the following article “Identify Deadlocks Using Graphical Deadlock Chain Event in SQL Server Profiler”.

How to Disable Trace Flags in SQL Server

You can execute the below TSQL Command to disable Trace Flag 1204 and 1222 at global level.

/* Disable Trace Flags 1204 and 1222 at global level */

DBCC TRACEOFF (1204,-1)
GO
DBCC TRACEOFF (1222,-1)
GO

/* Second Option Disable Trace Flags 1204 and 1222 using single DBCC TRACEON Statement at global level */

DBCC TRACEOFF (1204, 1222, -1)
GO

Conclusion

This article demonstrates different ways by which you can enable and disable trace flags 1204 and 1222 to capture detailed deadlock information in SQL Server Error Logs.