8

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?

Improve this question
13
  • 1
    Sure, possible, but all of their servers are already setup this way, I don't want to have to change them all, if I can get just this one to work correctly. As for not using both, also possible, but right now, the 1204 is the only way I knew that a deadlock occurred on C and the 1222 did not report it.
    – RBarryYoung
    Nov 4, 2014 at 20:54
  • 2
    I dont see a reason for enabling trace flags for deadlocks on sql 2008 up as xml_deadlock_report is already part of the system_health session. Check this post for more details. Check that to see if you can see the deadlocks.
    – Kin Shah
    Nov 4, 2014 at 22:52
  • 4
    @Kin One big downfall of the built-in deadlock report in system_health is that sql_text is not included, so it can be difficult to troubleshoot the queries/objects involved.
    – Aaron Bertrand
    Nov 4, 2014 at 23:43
  • 4
    @AaronBertrand I tried on 2008R2 RTM and it does give the sql text as well as the object name <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 used SELECT 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'
    – Kin Shah
    Nov 5, 2014 at 0:20
  • 1
    @kin I have seen the query cutoff using the system_health. It is a pain.
    – user507
    Nov 5, 2014 at 1:25

1 Answer 1

Reset to default
1

I had a similar issue, not sure it will sort yours.

Try this:

EXEC master..sp_altermessage 1205, 'WITH_LOG', TRUE;
GO

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:

select * from master.sys.messages
where text like '%deadlock%'

You can have more details here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.