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?

Community
  • 1
RBarryYoung
  • 3,041
  • 4
  • 22
  • 42
  • Is there any specific reason for using both trace flags to capture deadlock. 1222 is more recommended that 1204 . Have you though of using extended events trace to capture deadlock information. Just curious ? – Shanky Nov 04 '14 at 20:26
  • 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 04 '14 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 04 '14 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 04 '14 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 05 '14 at 00:20
  • Are they actually deadlocks or are they just long lasting blocks?? – swasheck Nov 05 '14 at 00:25
  • 1
    @kin I have seen the query cutoff using the system_health. It is a pain. –  Nov 05 '14 at 01:25
  • @kin yes, I am aware that I could use the system_health, or profiler, or extended events, etc., etc... All of these would require additional development, testing and deployment to integrate them into what the customer already has. I know how to do all of this and have done all of these at one time or another. That is not what I need help with. If I can correct the one broken server, that would be preferable to changing everything else. – RBarryYoung Nov 05 '14 at 15:24
  • @rbarryyoung my point was a workaround as the systemhealth session is already running by default. Have you even checked to see if you see a deadlock in that - which you are not seeing using trace flag? Again, I am trying to say that if you see the deadlock using systemhealth and not using trace flag then you might want to touch base with MS for that particular server. – Kin Shah Nov 05 '14 at 16:17
  • why would you use both at the same time? 1222 returns the same information, right? Just in xml format. I pull mine from systemhealth session, as referenced above. – rottengeek Dec 23 '14 at 15:39
  • If you have 2 servers and they are behaving differently have you checked that they are exactly the same version / patch level? – Ed Elliott Apr 07 '15 at 15:53
  • I'm wondering if the type of deadlock matters? Is it the same code/application/load on each server? MARS related deadlocks seem to be particularly difficult to dig into. Generating one yourself is the standard definition of a deadlock, but MARS is complicated, and I'm wondering if it is the difference between batchid and sbid... – rottengeek Apr 08 '15 at 19:49
  • @RBarryYoung - Have you looked in the logs to see if Server C is running some process that turns off the 1222 trace flag? – RLF Apr 10 '15 at 21:03

1 Answers1

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

Community
  • 1
Marcello Miorelli
  • 16,170
  • 52
  • 163
  • 300