Way to find when system health file is rolling over

Is there a way to find the events when system health extended event files are rolling over rather than manually monitoring for the events?

For my medium load server they stay upto 2-3 days. But for heavy loaded servers these files are rolling every 15 mins or so but no fixed pattern or timings. We know the reason why and working to filter out unwanted events or ones reported as issues.

I am curious if there is a way we can query at what time would the roll-over of files is happening. I do not see much documentation on MS docs as well but cant find this info?

Please suggest if its possible and how?

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);

SELECT 
    @file_name = 
        CAST(st.target_data AS xml).value(
            N'(EventFileTarget/File/@name)[1]', N'nvarchar(max)')
FROM sys.dm_xe_sessions s
    INNER JOIN sys.dm_xe_session_targets st
        ON s.[address] = st.event_session_address
WHERE 
    st.target_name = 'event_file'
    AND s.[name] = 'system_health';

SELECT @file_path = LEFT(
    @file_name,
    LEN(@file_name) - CHARINDEX('\', REVERSE(@file_name)) + 1);

SELECT
    files.[file_name],
    MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)[1]', N'datetime')) AS oldest_event
FROM sys.fn_xe_file_target_read_file
(
    @file_path + 'system_health*',
    null, null, null
) files
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:

SELECT
    files.[file_name],
    MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)[1]', N'datetime')) AS oldest_event_utc,
    SWITCHOFFSET
    (
        MIN(CAST(files.event_data AS XML).value(N'(event/@timestamp)[1]', N'datetimeoffset')), 
        DATENAME(TzOffset, SYSDATETIMEOFFSET())
    ) 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.

StackExchange Database Administrators Q#241302, answer score: 6