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

Deadlock Graph from the xml from system_health extended event Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2013 3:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:39 PM
Points: 127, Visits: 300
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

Post #1413374
Posted Wednesday, January 30, 2013 6:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:58 PM
Points: 7,127, Visits: 12,728
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
Post #1413615
Posted Wednesday, January 30, 2013 8:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:05 AM
Points: 283, Visits: 1,119
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
Post #1413667
Posted Wednesday, January 30, 2013 8:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:58 PM
Points: 7,127, Visits: 12,728
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
Post #1413830
Posted Wednesday, January 30, 2013 8:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:39 PM
Points: 127, Visits: 300
Hi,
We are on sql server 2008 R2 enterprose edition SP2

Thanks
Post #1413831
Posted Thursday, January 31, 2013 7:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:39 PM
Points: 127, Visits: 300
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
Post #1414141
Posted Thursday, January 31, 2013 9:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:05 AM
Points: 283, Visits: 1,119
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
Post #1414201
Posted Thursday, January 31, 2013 9:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:58 PM
Points: 7,127, Visits: 12,728
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
Post #1414212
Posted Thursday, January 31, 2013 9:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:05 AM
Points: 283, Visits: 1,119
I'm using pretty much every build from the last few years

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
Post #1414230
Posted Tuesday, April 9, 2013 7:58 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, October 13, 2014 11:46 AM
Points: 3,087, Visits: 1,436
Check this link.




My blog
Post #1440338
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse