When a deadlock occurs in Microsoft SQL Server, you can only see 1/2 of the deadlock picture from the Clarity logs. How do you find out the other side of the deadlock?

Article Id: 52176

Status: Published

Updated On: 21-03-2018 06:15

Legacy Id: TEC512140

Products:

CA Identity Manager , CA Identity Governance , CA Identity Portal , STARTER PACK-CLARITY PPM

Issue/Introduction:

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.

Environment:

Release:
Component: PPMENV