June 22, 2008 at 5:01 pm
The SQL Profiler is great for helping to fix deadlocks. Generally speaking, if I can capture the deadlock in Profiler and have a look at the deadlock graph, I can figure out what's gone wrong. Unfortunately, many of our deadlock problems are not deterministic and happen infrequently. Consequently, we often only see them in the production server and not during testing.
So, is there a way on a production server to capture things like the deadlock graph without the Profiler running?
Thanks!
Terry.
(coatta@acm.org)
June 22, 2008 at 10:50 pm
>> So, is there a way on a production server to capture things like the deadlock graph without the Profiler running?
Yes. Using "WMI event alert" it is possible.
June 22, 2008 at 11:43 pm
coatta (6/22/2008)
So, is there a way on a production server to capture things like the deadlock graph without the Profiler running?
Sure. Switch traceflag 1222 on and all deadlock graphs (in xml format) will be written to the SQL error log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2008 at 8:12 am
And, deadlocks are an error that can be captured with a TRY/CATCH statement in TSQL. You can try resubmitting the query a certain number of times before you finally give up and report an error (always limit retries because sometimes deadlocks occur within a single statement, resubmitting forever would not be good).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply