Information Technology Headline Animator

Information Technology

Saturday, January 28, 2012

How to Trace Deadlocks in SQL Server

In SQL Server Trace flags are  used to diagnose performance issues or to debug stored procedures or complex computer systems.Trace flags are valuable DBA tools that allow you to enable or disable a database function temporarily. Once you turn on a trace flag, it remains on until you either manually turn it off or restart SQL Server. So to Trace Deadlocks in SQL Server we use two Trace Flags

1. 1222

Used to trace deadlocks in SQL Server.

Syntax

DBCC TRACEON (1222,-1)

2. 3605

Used to write Deadlocks to Errorlog.

Syntax

DBCC TRACEON (3605,-1)


So above two Trace Flags are enough to Trace Deadlocks in SQL Server Databases. To Disable above Trace Flags we have to use DBCC TRACEOFF command.