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:
[cc lang=”sql”]
— SQL 2000 version
DBCC TRACEON (1204, -1)

— SQL 2005 version
DBCC TRACEON (1222, -1)
[/cc]

Turn off deadlock tracing:
[cc lang=”sql”]
— SQL 2000 version
DBCC TRACEOFF (1204, -1)

— SQL 2005 version
DBCC TRACEOFF (1222, -1)
[/cc]

This will globally turn deadlock tracing.

You can then verify if the traces are running using:

[cc lang=”sql”]
DBCC TRACESTATUS
[/cc]

tracestatus

5 comments
Amanda 29 Aug 2015 at 5:37 am

Thanks for explaining this.Just to add, I also got this error when I had a Collation sistenive column as Primary key in the source table and in the target table the column was Collation insensitive so the command failed with PRIMARY KEY violation.

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?

Joao 29 Aug 2015 at 8:47 am

SharePoint Config database log file too big. Solve itUSE SharePoint_ConfigGOALTER DATABASE SharePoint_Config SET RECOVERY SIMPLEDBCC SHRINKFILE(N’SharePoint_Config_log’, 1)ALTER DATABASE SharePoint_Config SET RECOVERY FULLGO

Dinesh Vishe 28 Mar 2013 at 11:47 am

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

Featured Articles

 Site Author

  • Thanks for visiting!
css.php