Wednesday, November 3, 2010

Deadlock Detection using sql server DBCC trace flags

Trace Flags can be used to output the details of deadlocks in a simple way(SQL Server only).They provide the maximum information to identify the root of the problem.The information will be recorded in the sql server' error log.
Enable trace flags 1204/1205 for more information about deadlocks.Refer this page for the list of trace flags available in SqlServer 2008

Example to turn on trace flag 1204
DBCC TRACEON (1204,-1)

To see the status of various trace flags and their scope
DBCC TRACESTATUS

Example to turn on trace flag 1204
DBCC TRACEOFF (1204,-1)

In the above example '-1' is to set the scope of trace flag globally

Now if the dead lock occurs details about the resources, queries invlolved in the lock will be recorded in the sql server Error Log.

Related
Collecting ODBC trace

No comments: