MyTechMantra.com

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.

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

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 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
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
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.

Ashish Mehta

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Add comment

Newsletter Signup!



Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips

Manning