SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlock Graph from the xml from system_health extended event


Deadlock Graph from the xml from system_health extended event

Author
Message
APA0876
APA0876
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2204 Visits: 400
How to get the Deadlock Graph from the xml extracted from system_health extended event.

I'm using SQL Server 2008R2 SP2, and when I try to open the xdl file it says Failed to initialize deadlock control, key cannot be nul, Parameter name:Key


Thanks
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94632 Visits: 14503
APA0876 (1/29/2013)
How to get the Deadlock Graph from the xml extracted from system_health extended event.

Retrieving Deadlock Graphs with SQL Server 2008 Extended Events By Jonathan Kehayias

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Richard Fryar
Richard Fryar
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: 1851 Visits: 1172
In some circumstances the workaround in the linked article for the invalid xml in earlier builds of SQL Server won't work. I've come across a few situations where a bit more manipulation is required.

This what I use now:


declare @xml xml

select @xml = target_data
from sys.dm_xe_session_targets
join sys.dm_xe_sessions on event_session_address = address
where name = 'system_health'

if (substring(cast (serverproperty('ProductVersion') as nvarchar(20)), 1, 5) = '10.00' and cast(substring(cast (serverproperty('ProductVersion') as nvarchar(20)), 7, 4) as int) < 2757)
or
(substring(cast (serverproperty('ProductVersion') as nvarchar(20)), 1, 5) = '10.50' and cast(substring(cast (serverproperty('ProductVersion') as nvarchar(20)), 7, 4) as int) < 1702)
begin
select cast (
replace (
replace (
replace (
replace (
replace (
replace(replace(replace(xev.xv.value ('(data/value)[1]', 'nvarchar(max)'), ' ','!£$%&'),'%&!£$',''),'!£$%&',' '),
'<victim-list', '<deadlock><victim-list'),
'<victim-list/>', '<victim-list>'),
'<process-list>', '</victim-list><process-list>'),
char(10), ''),
'"> <victimProcess', '" /> <victimProcess')
as xml)
from
(select @xml as tg) AS Data
cross apply tg.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') as xev (xv)
end
else
begin
select cast(xev.xv.value ('(data/value)[1]', 'nvarchar(max)') as xml)
from
(select @xml as tg) AS Data
cross apply tg.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') as xev (xv)
end




Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94632 Visits: 14503
Which version of SQL are you on?

http://www.sqlservercentral.com/Forums/FindPost1003578.aspx

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
APA0876
APA0876
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2204 Visits: 400
Hi,
We are on sql server 2008 R2 enterprose edition SP2

Thanks
APA0876
APA0876
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2204 Visits: 400
Hi,
The script sent does not work either to show the deadlock graph when I save it as a xdl file and open it with SSMS it says: Failed to initialize deadlock control.
There is an error in XML document (1,2)
<deadlock xmlns="> was not expected

Thanks
Richard Fryar
Richard Fryar
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: 1851 Visits: 1172
Did you read the linked article from opc.three's first reply? It states in there that "you can't save this XML document as a .XDL file and open it graphically in Management Studio"


Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com
Orlando Colamatteo
Orlando Colamatteo
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94632 Visits: 14503
opc.three (1/30/2013)
Which version of SQL are you on?

http://www.sqlservercentral.com/Forums/FindPost1003578.aspx

This one was actually directed to you Richard, just wondering if you were on the latest build. Jonathan seemed to imply there might have been multiple rounds of fixes for the XML format issues.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Richard Fryar
Richard Fryar
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: 1851 Visits: 1172
I'm using pretty much every build from the last few years Sad

I don't remember which build produced the xml that Jonathan's code couldn't handle, but I remember it was for cases where there is more than one deadlock victim.

Certainly since the builds that my script checks for I have seen no problems, though you're right about the incremental fixes from Microsoft - and there is still a size limit in the xml that can be returned I think.


Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12076 Visits: 1439
Check this link.




My blog

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