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.

2 comments
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

 Site Author