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?

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• Clarity PPM SaaS
• Clarity PPM On Premise

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