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
-
- DBCC TRACEOFF(1222,-1)
Note: In SQL Server 2000 the second argument is optional / illogical.
Note:
- Performance will not suffer with this deadlock detection approach as compared to any other alternative approach e.g. to run and monitor with a SQL Server Profiler all the time.
- SSMS view
 
This post was viewed (1159) times.
How Full is Your Bucket? Cultus ’07 [efi] for Sale