Traceflag 1222 for deadlocks
Use traceflag 1222 for deadlocks when monitoring SQL 2005+, and keep 1204 just for SQL 2000 instances. 1222 gives so much more information and doesn't cut off the SQL text.
Show info for Trace Flag 1204 and Trace Flag 1222 and also the deadlock graph from profiler.
Hello, we are in the process of implementing the use of trace flag 1222.
Currently, the “details” tab looks very similar to the existing trace flag 1204 (with the added benefit of the non-truncated SQL statement).
Also in the “output” tab one can view the complete 1222 message as it would appear in the error log.
We have a couple of questions:
Are there any extra parts of the original 1222 message that would be useful in a more formatted way in the details tab?
Are there any additional pieces of information, not already in the trace message, that would be beneficial to display in the details tab?
Feel free to reply directly to me or add a comment to this thread.
Brett Estes commented
Thank you, Mark. I use this blog as a reference point every time I evaluate deadlocks:
His highlighted information is the information I always focus on.
Well, SQL server 2008 and 2012 provides even better option for tracing deadlocks - Extended event "xml_deadlock_report". Output should be quite same but with less system overload that traceflag.
H Reyes commented
This will help a lot. The current one is truncating the SQL statements and the deadlock graph doesn't show up.
Unfortunately not at the moment Aaron. But we are reviewing it. I will update the forum as soon as we have made any progress on this.
Aaron Foote commented
This would be very good to have.
Any ETA on when we can expect this feature?
Don Ferguson commented
This is one that would really help. Can it be implemented in the next SQL Monitor 3 release?
We have this in our backlog so we're hopeful we can soon add this improvement to forthcoming releases...
Thanks for your votes