You can get all the
system_health event files and their oldest event like this:
DECLARE @file_name AS nvarchar(max);
DECLARE @file_path AS nvarchar(max);
CAST(st.target_data AS xml).value(
FROM sys.dm_xe_sessions s
INNER JOIN sys.dm_xe_session_targets st
ON s.[address] = st.event_session_address
st.target_name = 'event_file'
AND s.[name] = 'system_health';
SELECT @file_path = LEFT(
LEN(@file_name) - CHARINDEX('\', REVERSE(@file_name)) + 1);
MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)', N'datetime')) AS oldest_event
@file_path + 'system_health*',
null, null, null
GROUP BY files.[file_name]
OPTION(NO_PERFORMANCE_SPOOL, QUERYTRACEON 8649);
Note: for versions of SQL Server that don't support the
NO_PERFORMANCE_SPOOL query hint (prior to SQL Server 2016), you can replace that with with
QUERYTRACEON 8690 (see Spool operator and trace flag 8690 for details).
Hat tip to Erik Darling for suggesting the query hints, which sped things up significantly in my tests
The dates / times returned by that query are in UTC. You could use an approach like this one to convert to server local time:
MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)', N'datetime')) AS oldest_event_utc,
MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)', N'datetimeoffset')),
) AS oldest_event
The code above
- gets the filename for the currently-active event file,
- then attempts to extract the path using this method,
- then uses the
sys.fn_xe_file_target_read_file dynamic management function to get oldest event from each file
So one way to accomplish your goal would be to run that query as a scheduled agent job, and log the results to a table. Then you'd be able to see when the "oldest event" changes for each file (AKA when the file rolls over).
Recall that files can roll over for a number of different reasons.
If performance is an issue, and you are comfortable with PowerShell, you are likely much better off using the approach offered by Dan Guzman here.