Description:
When a deadlock occurs in Microsoft SQL Server, you are only seeing 1/2 of the deadlock picture from the Clarity logs. To get the other side of the deadlock, you can activate some additional tracing.
Solution:
In SQL Server this is done by enabling various trace flags and using the SQLDiag tool:
DBCC TRACEON(1204, -1) DBCC TRACEON(1205, -1) DBCC TRACEON(3605, -1)
1204 - Returns types of locks in the deadlock
1205 - Returns detailed information about the statements being run
3605 - Sends the trace info to the errorlog. This will then be outputted to a text file
(The -1 tells SQL Server to apply the trace flag to all connections)
After a deadlock, the data can be retrieved with SQLDiag. This can be run as a service or from the command line.
From the command line you could enter the following:
<SQL Server Home>\Binn\SQLDiag -E -O c:\temp\sqldiagoutput.txt
-E Tells it to use integrated security
-O Specifies and output directory
(The path of the output can be changed as needed.)
Keywords: deadlock, MS SQL Server, locks, sqldiag, traceon.