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