
/* System health is set to run at startup. It gets:
The sql_text and session_id for any sessions that encounters
  an error with severity >=20 
The sql_text and session_id for any sessions that encounter 
  a "memory" type of error such as 17803, 701, etc (we added this 
  because not all memory errors are severity >=20) 
A record of any "non-yielding" problems (you have sometimes seen 
  these in the ERRORLOG as Msg 17883) 
Any deadlocks that are detected 
The callstack, sql_text, and session_id for any sessions 
  who have waited on latches (or other interesting resources) 
  for > 15 seconds 
The callstack, sql_text, and session_id for any sessions 
  who have waited on locks for > 30 seconds 
The callstack, sql_text, and session_id for any session 
  that have waited for an extended period of time for 
  "external" waits or "pre-emptive waits". 
*/


-- all events generated from system health session
-- since SQL Server startup
select cast(xest.target_data as xml)
from sys.dm_xe_session_targets xest
join sys.dm_xe_sessions xes on xes.address = xest.event_session_address
where xest.target_name = 'ring_buffer' and xes.name = 'system_health'
