Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Does a deadlock get recorded in the log without trace flags? Expand / Collapse
Author
Message
Posted Friday, July 19, 2013 7:03 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 961, Visits: 4,991
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
Post #1475451
Posted Friday, July 19, 2013 7:31 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 42,479, Visits: 35,547
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 2008, MVP
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

Post #1475480
Posted Friday, July 19, 2013 7:45 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 961, Visits: 4,991
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
Post #1475489
Posted Friday, July 19, 2013 7:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 42,479, Visits: 35,547
If you just want to know if any occur, perhaps an agent alert on error 1205?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1475493
Posted Friday, July 19, 2013 8:54 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 12:24 PM
Points: 514, Visits: 1,717
Receive Deadlock Info via email
Post #1475536
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse