September 25, 2014 at 2:36 pm
I have been tasked at work to come up with a report that shows, by database, how many deadlocks have occurred and what the victor and victims are. We get SQL Monitor Alerts but i dont know of a way that i can query those results and place them into a report... I have been trying to use the trace files but i cant seem to get them into a good readable format to create this report. Does anyone know if we can query the RedGate database to get this information or any otherway...
Thank you.
September 26, 2014 at 3:35 am
There's nothing that Red Gate does that will capture this information. The one place I know you can get it without any changes to your system is the system_health extended events session. It captures deadlock information including the deadlock graph. You'll be able to query this directly, but it's XML.
Here's an introduction to system_health from Microsoft. Not sure this will work with 2008, but you can try, a set of reports on system_health. Here's a blog post [/url]that specifically discusses pulling deadlock info out of system_health.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 26, 2014 at 4:13 am
Please have a look here:-
http://dbafromthecold.wordpress.com/2014/02/11/who-are-your-victims/
It'll show you step by step how to monitor and alert Deadlocks. It'll also show you how to drop the information into a table that you can query.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply