Browse DevX
Sign up for e-mail newsletters from DevX


Resolving Deadlocks in SQL Server 2000 : Page 7

Your application can detect a deadlock and resubmit its transaction, but a better approach is to resolve a deadlock by changing the conditions that lead to it in the first place.

Gathering Deadlock Information
There are three basic strategies for gathering deadlock information. You can use the Perfmon Deadlocks/sec counter, inspect the output of the trace flag 1204, and use SQL Trace/Profiler's deadlock events.

Perfmon's Deadlocks/sec Counter
The Perfmon Deadlocks/sec counter is useful in measuring the frequency of deadlocking. It's also useful in multi-user testing to determine the minimum number of users required to reproduce deadlocks. However, it does not show individual deadlocks, it only shows that some deadlocking is occurring.

The 1204 Trace Flag
The output of the trace flag 1204 can be invaluable in diagnosing a deadlock. You set it in combination with the 3605 flag:
   DBCC TRACEON(3605, 1204)

The output of the flag will be sent to the SQL Server error log whenever SQL Server encounters a deadlock. You may find it useful to make a SQL Agent job execute these statements at startup so that the SQL Server error log will always report any deadlocks.

Interpreting the output of the trace flag 1204 can be challenging. Figure 1 shows a sample output and what some of the entries mean.

Ron Talmage heads Prospice, LLC, a database consulting firm based in Seattle, and is the author of "Microsoft SQL Server 7.0 Administrator's Guide" (Prima Publishing). He is a SQL Server MVP, PASS newsletter co-editor, and current president of the Pacific Northwest SQL Server Users Group, and also writes for SQL Server Professional and SQL Server Magazine. You can reach Ron by e-mail at rtalmage@prospice.com.
Thanks for your registration, follow us on our social networks to keep up-to-date