mthumphrey (8/15/2013)
It's almost like the system_health EE session has stopped collecting deadlock events on this particular server. The last one i have is 5 days ago. Meanwhile, with the trace flags set, I've had a couple each day in the SQL Error log, the latest was just a few hours ago.I agree this is a major disappointment. Extended events are great, but if you aren't confident you're capturing all the info, they become useless. I might try to set up a separate EE session that only collects deadlock events, just to see what it does.
Have you opened a Connect case with Microsoft? If so, let me know the ID and I will +1. I'm having this issue on 2008R2. I wonder if it is still an issue in 2012.
Thanks
-Matt
The funny thing is, progressively later deadlocks do appear eventually on the system_health xml, so the info is somewhere in the system, just not recorded in the system_health quickly enough. So it is not as if the events are lost, more that the updating of the system_health ring_buffer repository is not current/fast enough. At least that is my observation.
I have never opened a connect item with MS before; will try to find out how, but I am currently in the process of a support case with them anyway. Will update this thread when I have more news.
I was looking at this on a SQL-2012 instance yesterday; in sql 2012 system_health has 2 targets by default: ring_buffer (memory) and a file target (.xel file in the ERRORLOG folder). Perhaps it is better extracting this info from the file target? (but there might be the same issue there.)
Not sure if this issue is version-specific, but so far I have found it to be an issue mostly on SQL-2008 servers.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]