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?
xml_deadlock_report
is already part of thesystem_health session
. Check this post for more details. Check that to see if you can see the deadlocks.<inputbuf> BEGIN TRAN UPDATE dbo.DeadLockTest2 SET col1 = 1 UPDATE dbo.DeadLockTest SET col1 = 1 </inputbuf>
;mode="X" associatedObjectId="72057594039107584"
. Am I missing something ? I usedSELECT CAST(xet.target_data AS XML) AS XMLDATA FROM sys.dm_xe_session_targets xet JOIN sys.dm_xe_sessions xe ON (xe.address = xet.event_session_address) WHERE xe.name = 'system_health'