March 12, 2012 at 3:44 am
Briljant article 🙂
Pinned your name on my "must read" list !
April 5, 2012 at 6:37 am
How do you actually use the extended event to capture the graph and statement and email it for troubleshooting?
April 5, 2012 at 6:41 am
You'd have to write a process that query's the system_health session for graphs and then emails you them. There is no way to do it otherwise.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
April 9, 2012 at 4:14 pm
Jonathon - I took/adjusted your code along with someone else's modified version of your code, and have gotten stuck trying to figure out why it sometimes retrieves and sometimes doesn't. I was basically trying to pass in the Message Text from the 1205 alert to a Job kicked off from the alert, have the procedure strip the SPID from the text, and retrieve the deadlock report and email it to me. This is the code I was using. I The SPID / Date combination were essentially supposed to be adjusted to be 5-10 minutes ago, and the SPID from the 1205 error. I was creating a deadlock, then attempting to pull the report. It worked 1 time, then never again.
declare @ldt_StartDate datetime2 = dateadd(mi, -10, getdate())
,@li_ProcessID int = 1438
select cast(replace(replace(xEventData.xEvent.value('(data/value)[1]','varchar(max)'),'<victim-list>', '<deadlock><victim-list>'), '<process-list>','</victim-list><process-list>') as xml) as 'DeadlockGraph'
from (select cast(target_data as xml) as TargetData
from sys.dm_xe_sessions as s inner join sys.dm_xe_session_targets as t
on s.address = t.event_session_address
where s.name = 'system_health'
and t.target_name = 'ring_buffer'
) as i cross apply TargetData.nodes ('//RingBufferTarget/event') as xEventData(xEvent)
where xEventData.xEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
--and xEventData.xEvent.value('@timestamp', 'datetime') >= @ldt_StartDate
--and xEventData.xEvent.value('(data/value)[1]','varchar(max)') like '%spid="' + cast(@li_ProcessID as varchar) + '%'
April 9, 2012 at 4:55 pm
john.m.couch (4/9/2012)
Jonathon - I took/adjusted your code along with someone else's modified version of your code, and have gotten stuck trying to figure out why it sometimes retrieves and sometimes doesn't. I was basically trying to pass in the Message Text from the 1205 alert to a Job kicked off from the alert, have the procedure strip the SPID from the text, and retrieve the deadlock report and email it to me. This is the code I was using. I The SPID / Date combination were essentially supposed to be adjusted to be 5-10 minutes ago, and the SPID from the 1205 error. I was creating a deadlock, then attempting to pull the report. It worked 1 time, then never again.
If you are triggering this with a SQL Agent Alert, you should just flip that over to a WMI alert as shown in the books online, and then you have tokenized access to the graph from inside of the Job that responds to the alert. No point going back to Extended Events if you are trying to do alerting off SQL Agent Alerts.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
April 9, 2012 at 5:22 pm
I was under the impression there was a performance overhead with WMI Alerts. And I was really just looking for the textual output provided to send in the email. I also don't want to push the data to a table, I just wanted to email out the text and move on. I'll keep working on it.
April 9, 2012 at 5:32 pm
john.m.couch (4/9/2012)
I was under the impression there was a performance overhead with WMI Alerts. And I was really just looking for the textual output provided to send in the email. I also don't want to push the data to a table, I just wanted to email out the text and move on. I'll keep working on it.
WMI Alerts, as well as your Error Severity Alerts, piggy back on Event Notifications through msdb, so you are leveraging the same underlying mechanism either way. You can forgo all of that by just using an Event Notification as well. There is a full setup script in my PASS presentation demos from 2011 that you could use as well.
SQLskills Resource Page 2011 Conferences
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
April 10, 2012 at 9:10 pm
Jonathon,
I actually got it to work. I am attaching my code. Not the prettiest, but it works.
April 10, 2012 at 9:28 pm
john.m.couch (4/10/2012)
Jonathon,I actually got it to work. I am attaching my code. Not the prettiest, but it works.
This is incredibly inefficient when compared to the very simple implementation using Event Notifications from my last referenced link. Sometimes just because you can, doesn't necessarily mean that you should. The fact that you have to have a trace flag enabled, and then have to read the ERRORLOG file to do something that is natively possible using Event Notifications with neither of these steps makes this not make sense as a full solution.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
April 10, 2012 at 9:40 pm
I don't have any trace flags enabled. I am simply having this triggered from a 1205 alert, going and grabbing the last 1205 error that hit the log, and pulling the deadlock report for it.
Speaking of the Event Notification method, I was reading through your sample, looking at the code, it appears I could drop the use of the table and just send the email message as I am doing now.
Also, the only reason I pursued the method I did was to learn from it, and how to get what I was looking for. It is not an end all solution, merely a learning tool. I have to dig into things and play around to learn.
April 11, 2012 at 2:59 pm
OK, so, I used your code, and noted that in the code I have written that it was taken from your code provided in the PASS Summit, and I setup a Notification Event as you did. I have a couple of questions:
1. Used some logic found here, http://forums.overclockers.com.au/showthread.php?t=689566, thinking maybe I could attach the XML output to the email vs. just using the text version in the body??? Haven't seen a deadlock come through yet, but will shortly, without a doubt.
On a side note, thanks for all your guidance and information. Appreciate it.
April 11, 2012 at 8:58 pm
OK, dumb question, does the deadlock notification event you created only work on 1 database? Or does it apply to the whole server, and deadlock that occurs?
June 15, 2012 at 12:00 pm
I've tried every version of the code pasted on the page and consistently get this error:
Msg 9436, Level 16, State 1, Line 1
XML parsing: line 5, character 15, end tag does not match start tag
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 17, 2012 at 1:05 pm
Jonathan Kehayias (2/20/2009)
Comments posted to this topic are about the item <A HREF="/articles/deadlocks/65658/">Retrieving Deadlock Graphs with SQL Server 2008 Extended Events</A>
Whats the requirement for it to work on 2008 R2 version?
select @@VERSION
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
September 17, 2012 at 1:35 pm
What requirement? For the deadlock notification? Its the same as it is on reg 2008.
Viewing 15 posts - 31 through 45 (of 83 total)
You must be logged in to reply to this topic. Login to reply