Deadlock Graph from the xml from system_health extended event
-
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
-
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
-
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 -
Hi,
We are on sql server 2008 R2 enterprose edition SP2
Thanks
-
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
-
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"
-
opc.three (1/30/2013)
Which version of SQL are you on?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 -
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.
-
-
I'm using SSMS 2016 & 2012 -and still get the error : failed to initialize deadlock control key cannot be null
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply