Useful diagnostic events can already be waiting on the server when a complaint arrives. The system_health file target gives you a short built-in history that you can inspect with plain T-SQL.
Confirm That the Session Is Running
SQL Server includes the system_health Extended Events session on ordinary installations. It records selected diagnostic events without requiring you to create a new collection first. Its event files are useful when your own monitoring missed an incident.
The built-in session also exists on Azure SQL Managed Instance. Azure SQL Database does not provide the same built-in session, so these server-session queries do not apply there. The examples below target SQL Server on Windows.
I check the running session and its targets before searching for files. A stopped session has different diagnostic value from a running one. An empty current result should not become a confident statement that no problem occurred.
SELECT s.name AS SessionName, t.target_name,
TRY_CONVERT(xml, t.target_data) AS TargetXml
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
ON t.event_session_address = s.address
WHERE s.name = N'system_health';Look for the event_file target. The ring_buffer target is a separate in-memory representation with its own limits. Do not interpret a ring-buffer XML response as the full contents of the retained files.
Use the required diagnostic account and record the collection time. On recent SQL Server versions, relevant Extended Events diagnostic access requires server performance-state permissions. Follow the established access process rather than modifying the built-in session to avoid a permission error.
Locate the system_health File Target on Disk
The target data contains the current event filename. Read it rather than guessing an installation directory. Named instances and customized paths make a hard-coded Program Files location unreliable.
This script finds the current file and builds a Windows-folder wildcard for the system_health rollover files. Inspect the returned file and pattern before using them for a wider investigation.
DECLARE @CurrentFile nvarchar(260);
SELECT @CurrentFile = c.TargetXml.value(
'(EventFileTarget/File/@name)[1]', 'nvarchar(260)')
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t ON t.event_session_address = s.address
CROSS APPLY (SELECT TRY_CONVERT(xml, t.target_data) AS TargetXml) AS c
WHERE s.name = N'system_health' AND t.target_name = N'event_file';
IF @CurrentFile IS NULL OR CHARINDEX(N'\', @CurrentFile) = 0
THROW 50020, 'Inspect the running event_file target and its Windows path first.', 1;
DECLARE @Pattern nvarchar(260) =
LEFT(@CurrentFile, LEN(@CurrentFile) - CHARINDEX(N'\', REVERSE(@CurrentFile)) + 1)
+ N'system_health*.xel';
SELECT @CurrentFile AS CurrentFile, @Pattern AS RetainedFilePattern;
SELECT TOP (2000) object_name AS EventName, timestamp_utc,
file_name, file_offset, TRY_CONVERT(xml, event_data) AS EventXml
INTO #HealthEvents
FROM sys.fn_xe_file_target_read_file(@Pattern, NULL, NULL, NULL)
WHERE object_name IN (N'error_reported',
N'connectivity_ring_buffer_recorded', N'wait_info')
ORDER BY timestamp_utc DESC;The timestamp_utc result column is available starting with SQL Server 2017. The example therefore requires that version or later. Earlier versions need the event timestamp read from event_data instead.
The event XML remains available alongside the parsed fields. That is important when an event's payload differs by version. File name and offset also give you a concrete reference back to the retained record.
TOP limits returned rows, not necessarily the amount of file data read to find them. Keep the pattern narrow and avoid collecting a large unrelated archive during a busy incident. A result cap is not an IO guarantee. The WHERE clause keeps only the three event types the later queries read. Without it, frequent memory broker records can fill the whole 2,000-row cap and leave the later queries empty.
Pull Serious Error Details From the XML
error_reported events carry severity and message fields. Filter severity 17 and above for the serious-error review requested here. Read the error number and message together rather than relying on severity alone.
SELECT timestamp_utc,
EventXml.value('(/event/data[@name="error_number"]/value)[1]', 'int') AS ErrorNumber,
EventXml.value('(/event/data[@name="severity"]/value)[1]', 'int') AS Severity,
EventXml.value('(/event/data[@name="message"]/value)[1]', 'nvarchar(4000)') AS ErrorMessage,
file_name, file_offset
FROM #HealthEvents
WHERE EventName = N'error_reported'
AND EventXml.value('(/event/data[@name="severity"]/value)[1]', 'int') >= 17
ORDER BY timestamp_utc DESC;The session captures selected conditions, not every possible application failure. An error outside its configured predicates will not appear simply because you ask for it later. Inspect the active session definition when coverage is uncertain.
A serious error is evidence at its recorded instant. It does not establish how long a resource shortage lasted or which application caused it. Correlate the message and timestamp with other diagnostics before assigning a cause.
The message can include sensitive operational detail. Keep the output in the approved diagnostic location and restrict its distribution. Read only the fields needed for the investigation instead of exporting unrelated payloads casually.
Inspect Connectivity and Selected Wait Records
The session includes connectivity_ring_buffer_recorded and wait_info events under its configured collection rules. Their payloads answer different questions from error_reported. Preserve the raw data elements before choosing version-specific fields to extract.
SELECT EventName, timestamp_utc,
EventXml.query('/event/data') AS EventDetailsXml,
file_name, file_offset
FROM #HealthEvents
WHERE EventName IN (N'connectivity_ring_buffer_recorded', N'wait_info')
ORDER BY timestamp_utc DESC;A connectivity record can describe a connection-related condition that deserves follow-up. It does not supply a complete application request trace. Match the time with connection logs and the reported failure before deciding they describe the same attempt.
Wait records represent the selected waits captured by this session's definition. They are not a substitute for all session waits or instance wait statistics. Read the event's units and fields before comparing its duration with another diagnostic source.
I keep each event name beside its parsed fields. Otherwise, similarly named values from different event types become easy to mix together. The server already supplied a useful label; the report should keep it.
Rollover and Coverage in the system_health File Target
Event files roll over as the target reaches its configured size and retained-file limit. Busy periods can shorten the available history. Missing older events therefore do not prove that an earlier incident never happened.
Do not change system_health's settings merely to make this query return a longer timeline. If a recurring problem needs different coverage, plan a separate focused collection. The built-in session should keep its understood diagnostic role.
Which time zone does the caller's incident report use? Event timestamps are UTC. Convert deliberately for presentation and retain the original UTC value so daylight-saving changes do not create ambiguous comparisons.
Preserve a Small Useful Incident Record
Save the relevant event rows promptly with their file references, session state, and collection time. Keep the exact filter used to produce them. A later rollover can remove the same records from the active target.
Compare those events with the error log, operating-system signals, and application evidence appropriate to the symptom. One built-in recorder cannot answer every question. Its value is the evidence already present before you arrange a targeted collection.
The session is a helpful witness with a short memory. Use what it retained, state what it did not cover, and avoid turning a quiet result into a blanket health certificate.
When several retained files contain events from the selected period, keep their names in the export. Rollover order is not a substitute for the event timestamp, and identical event types can recur across files. Use the filename and offset together when tracing one specific record. That gives a reviewer an exact location instead of asking them to search an entire XML collection again.
The system_health file target is a retained diagnostic source with finite coverage. Capture the relevant events before rollover removes the evidence from the system_health file target.
Related reading on this blog: Reading Deadlock Graphs From the system_health Session and Capturing Stored Procedure Executions with Extended Events in SQL Server.
A retained event file is not a complete incident history, it is a useful window into the conditions the session chose to record.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
