Deadlocks are a not so wonderful unnaturally occurring event that all DBAs will eventually have the pleasure to take a look at. Since deadlocks are time senisitive it is important that at the time of the deadlock the correct mechanisms are in place to capture the detail of the deadlock in the SQL Server error log.
For SQL Server 2000 the trace flags to use are 1204 and 3605. And for SQL Server 2005, you can use the same flags or one up them with 1222 which produces similar results but in a much cleaner output.
Since there are different trace flags between 2000 and 2005 there are of course different attack plans for resolving deadlocks. A couple of the better links for SQL Server 2000 are:
- INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
- SQL Server technical bulletin – How to resolve a deadlock
Some SQL Server 2005 resources:
- Detecting and Ending Deadlocks
- Analyzing Deadlocks with SQL Server Profiler
- Deadlock Troubleshooting, Part 1
- Deadlock Troubleshooting, Part 2
- Deadlock Troubleshooting, Part 3
But really the key to deadlocks is really not having them in the first place:
That’s all for now…
At least I figured out the deadlocks I was working on this morning…