Post reply

Does a deadlock get recorded in the log without trace flags?

  • July 19, 2013 at 7:03 am

    #277624

    Not the entire deadlock information that's returned when you enable a trace flag (1204 or 1222,) but at least something like "deadlock occurred between SPID X and Y?" Something I can quickly find in the logs?

    Based on this: http://www.brentozar.com/archive/2011/07/difficulty-deadlocks/[/url]

    It sounds like the answer is yes.

    I'm asking, as I've got one server which was experiencing LOTS of deadlocks, I did have the trace flag 1222 enabled for a time and was using this: http://blogs.msdn.com/b/bartd/archive/tags/sql+deadlocks/default.aspx to troubleshoot. Since then (and updating statistics, which I should've done sooner seeing as said DBs were migrated from an old server...) the number of deadlocks has fallen precipitously (as in I haven't seen one in a week) so I've turned off the trace flag.

    I've been checking using XEs but I'd like to double check myself, and the SQL Log would be ideal...

    Thanks,

    Jason

  • July 19, 2013 at 7:31 am

    #1634019

    Without a traceflag, there's nothing written into the SQL errorlog.

    The deadlock error is sev 16, not enough to be logged. You can set up an agent alert for it or, since you're on SQL 2008 you can poll the system health extended events session and get the entire deadlock graph.

    The system health session is much like the default trace, it's a circular file so any deadlocks caught aren't kept forever.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 19, 2013 at 7:45 am

    #1634028

    OK, thanks!

    I've been watching the Extended Events, but knowing they may not catch everything, I was hoping for a second method (without the trace flag)

    Knowing XEs don't stay forever (seeing as right now it's the ring buffer I'm pulling the deadlock info from) is OK, I just need to keep an eye out for a resurgence of deadlocks...

    Thanks again!

    Jason

  • July 19, 2013 at 7:48 am

    #1634032

    If you just want to know if any occur, perhaps an agent alert on error 1205?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 19, 2013 at 8:54 am

    #1634070

    Receive Deadlock Info via email[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply