I have a customer site with two similarly configured 2008r2 SQL Servers "A" and "C". On both servers the trace flags 1204 and 1222 are enabled and DBCC tracestatus shows the following on both servers:
TraceFlag Status Global Session
1204 1 1 0
1222 1 1 0
3605 1 1 0
On A the trace flags work as expected, when a deadlock occurs, we get both the 1204 and 1222 deadlock reports in the error logs. However, on C, only the 1204 report shows up, we never get the 1222 report.
For the life of me I cannot see any reason for this difference. I have both googled this extensively, and read (and re-read) the MS doc on these trace flags, and I cannot find any reports of behavior like this, nor any hints as to what might cause it. The only thing that comes close is the occasional claim that neither trace flag was working, but these all turned out to be cases were they had typos in the enabling commands. I know that this is not the case here because I have used DBCC TRACESTATUS to confirm it.
So any insights into what might be causing only trace flag 1222 to not work and/or how to fix it would be greatly appreciated.
Well, here's an interesting development. Whenever I generate a deadlock myself (using this code: https://stackoverflow.com/questions/7813321/how-to-deliberately-cause-a-deadlock), then I get both trace reports in the error logs. It's only the "natural" deadlocks that occur every couple of days from the applications that seem to only trigger one of the deadlock reports. Not sure if this helps, is there any reason to believe that trace 1222 would not report on all of the same deadlock conditions that 1204 would?
Best Answer
I had a similar issue, not sure it will sort yours.
Try this:
Even though it was logging in the event log via the trace flag, this also needs to set in order to trigger the emails. You can see the table here:
You can have more details here