CHROMIUM

Welcome to CHROMIUM's very own blog!

Deadlock Detection (SQL Server)

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior, with negligible over head on SQL Server. Database Deadlocks can easily be logged and rectified with the help of simple illustrated steps described in this document.

 

How to Methodology Summary:

Trace flags would be set enabled on the server on which deadlocks are more frequent than normal. These trace flags would log Dead locks information into SQL Server Event Logs which afterwards would be used for the analysis of deadlock. This information will provide exact problem description of problem i.e. what caused the deadlock occurrence.

Steps:

1.       Execute following command in SQL Server Management Studio (SSMS) [SQL Server 2005 onward] or in SQL Server Query Analyzer (SQL Server 2000)

a.       DBCC TRACEON(1222,-1)

Note: In SQL Server 2000 the second argument is optional / illogical.

2.       Verify status of set trace flags with following command

a.       DBCC TRACESTATUS(-1)

The output should contain following row related to Trace Flag 1222

TraceFlag         Status      Global      Session

1222        1           1           0

Note: In SQL Server 2000 output may not contain Global and Session columns. Proceed with daily routine work on application without any hesitation, and if in any case dead lock occurs the information will be automatically logged in the SQL Server Log.

 

4. Just need to execute the following command to pull back information from the SQL Server Log

a. EXEC SP_READERRORLOG

 

5. Proceed with daily routine work on application without any hesitation.

6. After analysis/resolution, the Trace Flags should be reverted back to default settings, with the help of following command

    1. DBCC TRACEOFF(1222,-1)

Note: In SQL Server 2000 the second argument is optional / illogical.

 

Note:

 

 

This post was viewed (1137) times.

Leave a Reply

Your email address will not be published. Required fields are marked *