Turn On Deadlock Trace Flag
-
Posted on June 17, 2009 by Derek Dieter
-
0
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]
- Comments (RSS)
- Trackback
- Permalink
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.
What is global and session ?
How can I disable a table lock when I execute an update on many records with Rowlock?
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
we can check unbaled trace flag using DBCC TRACESTATUS();
