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

  • 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[/url]

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • 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?

    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

  • 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 this link[/url].

  • 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