Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
jasona.work
jasona.work
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1877 Visits: 10900
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/
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47183 Visits: 44356
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


jasona.work
jasona.work
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1877 Visits: 10900
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47183 Visits: 44356
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


Geoff A
Geoff A
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 1790
Receive Deadlock Info via email
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search