Retrieving Deadlock Graphs with SQL Server 2008 Extended Events

Question

Retrieving Deadlock Graphs with SQL Server 2008 Extended Events

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

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

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 1

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: 6976 More actions · Answer 2

Tac11

SSCertifiable

Points: 6976

April 28, 2015 at 9:23 am

#1793974

How do I delete previous deadlock report?

Tac11 SSCertifiable Points: 6976 More actions · Answer 3

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: 453 More actions · Answer 4

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: 453 More actions · Answer 5

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: 6976 More actions · Answer 6

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

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 One Orange Chip Points: 26778 More actions · Answer 8

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

coolchaitu SSChampion Points: 10332 More actions · Answer 9

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