2

I am using the following query to retrieve deadlock xml.

select create_time,XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph
FROM
(select s.create_time,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' ***AND s.create_time > GETDATE()-3***) AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

query from Retrieving Deadlock Graphs with SQL Server 2008 Extended Events.

The only thing I have modified is adding create_time field and filtering it for past 3 days

The query does not give me the data for a week. It gives me data for 5/4/2017 but not for today 5/12/2017.

I can see deadlock information logged in Error log.

How do I retrieve deadlock xml for today? I can read the error log. However, I am interested in getting it in xml.

SQL Server Version: 2008 R2 SP2

Marco
  • 3,710
  • 5
  • 23
  • 31
Santhoshkumar KB
  • 591
  • 2
  • 7
  • 22
  • Although I don't think it's the problem here, note s.create_time is the creation time of the extended event session not the events themselves. For why you might be missing events, this might help https://www.sqlskills.com/blogs/jonathan/why-i-hate-the-ring_buffer-target-in-extended-events/ – Gareth Lyons May 12 '17 at 13:34
  • That explains it. Thank you for sharing the link. Is there any workaround to retrieve deadlock in xml? – Santhoshkumar KB May 12 '17 at 13:46
  • Use event notification - see my answer here – Kin Shah May 12 '17 at 14:08
  • No problem. There's a link in the article I linked to a post by a CSS engineer, lots of useful information there. Best one is probably to create your own XE session that doesn't use the ring buffer. – Gareth Lyons May 12 '17 at 15:23
  • @Kin, do we have to enable WMI somewhere in configuration? I will give a try on this without xp_cmdshell. – Santhoshkumar KB May 12 '17 at 15:31
  • @SanthoshkumarKB no .. you dont have to enable WMI. if you dont want deadlock graphs emailed, then you can disable xp_cmdshell ..In the answer, I have a comment at the bottom to address that. – Kin Shah May 12 '17 at 15:33

0 Answers0