Turn On Deadlock Trace Flag

It is often times difficult to find the culprits of a deadlock. SQL Server implemented a trace flag since SQL Server 2000 that ouputs the inputbuffer in order to find the culprits. The output of these traces will be sent to the SQL Server Error Log, found in Management ยป SQL Server Logs.

There are two versions of the trace flag. The best one to use is the 1222, which only works on SQL 2005+. The major difference between the two is that the old one truncated the results at times and was hard to read.

I have found no performance impact at all when enabling these on production servers.

Turn on deadlock tracing:

-- SQL 2000 version
DBCC TRACEON (1204, -1)

-- SQL 2005 version
DBCC TRACEON (1222, -1)

Turn off deadlock tracing:

-- SQL 2000 version
DBCC TRACEOFF (1204, -1)

-- SQL 2005 version
DBCC TRACEOFF (1222, -1)

This will globally turn deadlock tracing.

You can then verify if the traces are running using:



Dinesh vishe 13 Mar 2015 at 9:55 am

What is global and session ?

Gilberto B M 23 Dec 2013 at 8:16 pm

How can I disable a table lock when I execute an update on many records with Rowlock?

Dinesh Vishe 28 Mar 2013 at 11:47 am

we can check unbaled trace flag using DBCC TRACESTATUS();

Featured Articles

Learn Index Tuning!

Index Tuning Video Training
only $49
You'll learn everything practical there is to know about index tuning with this downloadable video.
Learn more

 Site Author

  • Thanks for visiting!