Retrieving Deadlock Graphs with SQL Server 2008 Extended Events

  • SQLBlimp

    SSCertifiable

    Points: 6322

    Jonathan Kehayias (10/20/2014)


    SQLSentry Plan Explorer Pro already does this. You have to have a pro edition license though

    Thanks again! I will evaluate it.

    JT

  • Tac11

    SSCertifiable

    Points: 6799

    How do I delete previous deadlock report?

  • Tac11

    SSCertifiable

    Points: 6799

    when I run your query on my laptop getting this error:

    Msg 9436, Level 16, State 1, Line 7

    XML parsing: line 5, character 15, end tag does not match start tag

  • AnthonyR

    SSC-Addicted

    Points: 447

    Tac11 (4/28/2015)


    How do I delete previous deadlock report?

    They're coming from a DMV (Dynamic Management View) so you cannot delete them, per se. All DMVs retain their data until either the SQL Service gets restarted or the hosting Windows Server gets restarted.

  • AnthonyR

    SSC-Addicted

    Points: 447

    Tac11 (4/28/2015)


    when I run your query on my laptop getting this error:

    Msg 9436, Level 16, State 1, Line 7

    XML parsing: line 5, character 15, end tag does not match start tag

    If your database contains objects with Unicode names, or executes raw SQL from a middle tier that can pass through Unicode characters, you might want to try this version of the query from the original article.

    select cast(replace(replace(

    XEventData.XEvent.value('(data/value)[1]', 'nvarchar(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_session_targets st

    join sys.dm_xe_sessions s on s.address = st.event_session_address

    where name = 'system_health'

    ) as Data

    cross apply TargetData.nodes ('//RingBufferTarget/event') as XEventData (XEvent)

    where XEventData.XEvent.value('@name', 'nvarchar(4000)') = 'xml_deadlock_report'

    If you're still experiencing errors please post the actual SQL statement that you're executing so that someone can help pinpoint the error for you.

  • Tac11

    SSCertifiable

    Points: 6799

    It worked. Thanks you. You guys (sqlservercentral users) are awesome.

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    AnthonyR (4/28/2015)


    Tac11 (4/28/2015)


    How do I delete previous deadlock report?

    They're coming from a DMV (Dynamic Management View) so you cannot delete them, per se. All DMVs retain their data until either the SQL Service gets restarted or the hosting Windows Server gets restarted.

    ALTER EVENT SESSION system_health ON SERVER STATE=STOP;

    ALTER EVENT SESSION system_health ON SERVER STATE=START;

    The ring_buffer is an in memory target and can be cleared by simply stopping the event session and starting it again using the above commands. No need to restart the server at all.

    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]

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    Tac11 (4/28/2015)


    when I run your query on my laptop getting this error:

    Msg 9436, Level 16, State 1, Line 7

    XML parsing: line 5, character 15, end tag does not match start tag

    What is your build number for SQL Server from SELECT @@VERSION? You are hitting a bug in the xml_deadlock_report XML output that was fixed and you are really behind on patching if you are getting that error:

    https://support.microsoft.com/en-us/kb/978629

    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]

  • coolchaitu

    SSChampion

    Points: 10269

    Good morning Jonathan,

    I am getting below error on running the script:

    Msg 9455, Level 16, State 1, Line 1 

    XML parsing: line 2, character 233, illegal qualified name character

Viewing 9 posts - 76 through 84 (of 84 total)

You must be logged in to reply to this topic. Login to reply