Skip to main content
add a link to a quoted answer
Source Link
Marcello Miorelli
  • 16.6k
  • 53
  • 170
  • 311

The accepted answeraccepted answer did not work for me consistently. The ring buffer apparently is known to drop events in certain circumstances..

ConnectItem

Ring Buffer Issues

The system_health log event files can parsed (from this answer):

with XmlDeadlockReports as
(
  select convert(xml, event_data) as EventData
  from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
  where substring(event_data, 1, 50) like '%"xml_deadlock_report"%'  
) 
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp,
       EventData.query('event/data/value/deadlock') as XdlFile
  from XmlDeadlockReports
 order by TimeStamp desc

The XdlFile field can be saved to an .xdl file and read into SSMS. Tested in Sql Server 2012.

The accepted answer did not work for me consistently. The ring buffer apparently is known to drop events in certain circumstances..

ConnectItem

Ring Buffer Issues

The system_health log event files can parsed (from this answer):

with XmlDeadlockReports as
(
  select convert(xml, event_data) as EventData
  from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
  where substring(event_data, 1, 50) like '%"xml_deadlock_report"%'  
) 
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp,
       EventData.query('event/data/value/deadlock') as XdlFile
  from XmlDeadlockReports
 order by TimeStamp desc

The XdlFile field can be saved to an .xdl file and read into SSMS. Tested in Sql Server 2012.

The accepted answer did not work for me consistently. The ring buffer apparently is known to drop events in certain circumstances..

ConnectItem

Ring Buffer Issues

The system_health log event files can parsed (from this answer):

with XmlDeadlockReports as
(
  select convert(xml, event_data) as EventData
  from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
  where substring(event_data, 1, 50) like '%"xml_deadlock_report"%'  
) 
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp,
       EventData.query('event/data/value/deadlock') as XdlFile
  from XmlDeadlockReports
 order by TimeStamp desc

The XdlFile field can be saved to an .xdl file and read into SSMS. Tested in Sql Server 2012.

Source Link
crokusek
  • 2.1k
  • 4
  • 24
  • 33

The accepted answer did not work for me consistently. The ring buffer apparently is known to drop events in certain circumstances..

ConnectItem

Ring Buffer Issues

The system_health log event files can parsed (from this answer):

with XmlDeadlockReports as
(
  select convert(xml, event_data) as EventData
  from sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
  where substring(event_data, 1, 50) like '%"xml_deadlock_report"%'  
) 
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp,
       EventData.query('event/data/value/deadlock') as XdlFile
  from XmlDeadlockReports
 order by TimeStamp desc

The XdlFile field can be saved to an .xdl file and read into SSMS. Tested in Sql Server 2012.