Tracing deadlock with Trace Flag 1222 and Trace Flag 1204


In previous article, we have already learned that how we can trace a deadlock situation with SQL Server Profiler. In case you don’t read, click here. And to understand locking, blocking and deadlocking in sql server, click here. In this post we will learn how we can use “trace flag 1204 and trace flag 1222” to trace a deadlock.

In this article we are going to learn how we can trace SQL Server deadlock and extract the deadlock information from captured data. We will use DBCC TRACEON command, Trace Flag 1204 and Trace Flag 1222 to capture the details.

What is DBCC (Database Console Commands)?

DBCC statements act as Database Console Commands for SQL Server. We can run it using TSQL programming language. It provides commands related to various categories like Maintenance, Miscellaneous,  Informational and Validation.

Deadlock

As we know that Deadlock in SQL Server is a situation of permanent blocking where two or more processes are waiting for other resource locked by each other with holding lock on their own resource. It creates a circular chain and permanent block because of incompatible lock request and lock hold on resources.

Trace Flag 1204 and Trace Flag 1222

To capture the details related to deadlock in SQL Server, we can use Trace Flag 1204 and Trace Flag 1222 or any of them. Trace Flag 1222 and 1204 both capture these information in SQL Server Error Log.

Trace Flag 1204 – Provides details of nodes involved in the deadlock
Trace Flag 1222 – Produce deadlock information in XML format

Using Trace Flag 1222

We can make this ON in two ways, first we can use query editor window with “DBCC TRACEON (1222, -1)” command. But remember that this trace flag will not capture the deadlock information on next start of SQL Server if we start it from T-SQL DBCC command. To enable capturing deadlock information even SQL Server restarts, use another way, using startup parameters in SQL Server. In this topic we are going to use first approach.

Use below command in a query editor window;

DBCC TRACEON (1222, -1)

 

-1 : Switches specified trace flags globally

After executing above command, you will get below message;

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now create a demo deadlock as discussed in the section “How to create a demo purpose deadlock?” in post Tracing deadlock in sql server profiler.

Once you will get error message of deadlock, go to Object Explorer -> Management -> SQL Server Logs

Trace Flag 1222 and Trace Flag 1204

From above figure, you can see that there are 6 Archives and 1 Current log file. SQL Server Error Logs holds upto 6 archived log files.
To see the TRACE FLAG 1222 Output, right click on top “Current – Current Date Time” file and click on “View SQL Server Log”. Log file viewer window will appear. Have a look on the result of trace flag 1222 below.

Trace Flag 1222 output

Trace Flag 1222 and Trace Flag 1204

In above figure you can see, all the details related to deadlock has been captured in Error Log.

Using Trace Flag 1204

DBCC TRACEON(1204, -1)

Use above command to enable Trace Flag 1204. Again create a deadlock situation as above and once you will get error message of deadlock, go to Object Explorer -> Management -> SQL Server Logs.

Have a look on the output of trace flag 1204 below.

Trace Flag 1204 output

Trace Flag 1222 and Trace Flag 1204

 

All the details related to Trace Flag 1204 has been captured in Error Log.

Finally to disable these traces on your machine, use these commands;

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

Thanks for your patience for reading this article and do rate and share this post.

Rate This
[Total: 1    Average: 5/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.

Leave a comment

Your email address will not be published. Required fields are marked *

*