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.
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.