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.
Popular search terms:
