"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."







Follow @MyTechMantra on Twitter
We're on Facebook





Identify Deadlocks in SQL Server Using Trace Flag 1222 and 1204


Jan 29 2014

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

Important Note

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

Enable Trace Flag 1222 and 1204 in SQL Server Using Startup Parameters

Specify the Trace Flag –T1204 and –T1222 one by one as shown in the above snippet 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.

Restart SQL Server Database Engine Service for Trace Tlag settings at global level to come into effect

Identify all trace flags which are currently enabled globally

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

Currently Enabled Trace Flags in SQL Server

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.

SQL Server Deadlock Information Captured in SQL Server Error Log File

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.


Share this Article