Way to find when system health file is rolling over

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

Screenshot of SSMS results

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.


Below is a powershell example that summarizes events by name from the local system_health target files using the new Microsoft [SqlServer.XEvent PowerShell module][1]. I've found it's much faster to process high event volumes using .NET/PowerShell than parsing XML in T-SQL. You can schedule this as a SQL Agent job to identify what is driving the event activity and take corrective action if needed.

# Install the SqlServer.XEvent module from an admin PowerShell session before running this script:
# Install-Module -Name SqlServer.XEvent

# get list of system_health trace files
Function Get-XeFiles() {
    $connectionString = "Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI";
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString);
    $connection.Open();
$query = @"
WITH
      --get full path to current system_health trace file
      CurrentSystemHealthTraceFile AS (
        SELECT CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') AS FileName
        FROM sys.dm_xe_session_targets
        WHERE
            target_name = 'event_file'
            AND CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') LIKE '%\system[_]health%'
    )
      --get system_health trace folder 
    , TraceDirectory AS (
        SELECT 
            REVERSE(SUBSTRING(REVERSE(FileName), CHARINDEX(N'\', REVERSE(FileName)), 255)) AS TraceDirectoryPath
        FROM CurrentSystemHealthTraceFile
        )
SELECT TraceDirectoryPath
FROM TraceDirectory;
"@

    $command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
    $traceFileDirectory = $command.ExecuteScalar()
    $connection.Close()

    $xe_files = Get-Item "$($traceFileDirectory)system_health_*.xel"
    return $xe_files

}

try {

    $xe_files = Get-XeFiles
    foreach($xe_file in $xe_files) {
        try{
            # summary of events by event_name for each file
            $events = Read-SqlXEvent -FileName $xe_file.FullName
            Write-Host "Summary for file $($xe_file.FullName)"
            $events | Group-Object -Property Name -NoElement | Format-Table -AutoSize
        }
        catch {
            if(($_.Exception.GetType().Name -eq "AggregateException") -and ($_.Exception.InnerException -ne $null) -and ($_.Exception.InnerException.GetType().Name -eq "IOException")) {
                # ignore error due to active trace file
                Write-Host "$($_.Exception.InnerException.Message)"
            }
            else {
                # rethrow other errors
                throw
            }
        }
    }

}
catch {
    throw
}

Sample output:

Summary for file D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\system_health_0_132057213063750000.xel

Count Name                                                
----- ----                                                
  860 sp_server_diagnostics_component_result              
 1072 scheduler_monitor_system_health_ring_buffer_recorded
    2 connectivity_ring_buffer_recorded                   
    1 security_error_ring_buffer_recorded         

Summary for file D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\system_health_0_132057856050380000.xel

Count Name                                                
----- ----                                                
 1312 sp_server_diagnostics_component_result              
 1644 scheduler_monitor_system_health_ring_buffer_recorded
   28 scheduler_monitor_non_yielding_ring_buffer_recorded 
    4 connectivity_ring_buffer_recorded                   
    2 error_reported                                      
    2 wait_info                                           
    6 security_error_ring_buffer_recorded     

EDIT:

This can also be done remotely and against multiple servers with a single script as long as the trace folder is available remotely via a share. The example below runs against each server in a list and uses a UNC path to access the trace files. Because this version uses the drive letter admin share, it must run under a Windows account that has Windows admin permissions on the remote box. A less privileged account can be used if you create a share on each server and use that share name instead.

# get list of system_health trace files with admin share UNC path
Function Get-XeFiles($serverName) {
    $connectionString = "Data Source=$serverName;Initial Catalog=tempdb;Integrated Security=SSPI";
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString);
    $connection.Open();
$query = @"
WITH
      --get full path to current system_health trace file
      CurrentSystemHealthTraceFile AS (
        SELECT CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') AS FileName
        FROM sys.dm_xe_session_targets
        WHERE
            target_name = 'event_file'
            AND CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') LIKE '%\system[_]health%'
    )
      --get system_health trace folder 
    , TraceDirectory AS (
        SELECT 
            REVERSE(SUBSTRING(REVERSE(FileName), CHARINDEX(N'\', REVERSE(FileName)), 255)) AS TraceDirectoryPath
        FROM CurrentSystemHealthTraceFile
        )
SELECT TraceDirectoryPath
FROM TraceDirectory;
"@

    $command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
    $traceFileDirectory = $command.ExecuteScalar()
    # change driver letter to admin share UNC path (e.g. "D:\" to "\\servername\d$")
    $traceFileDirectory = "\\$serverName\$($traceFileDirectory.Replace(":", "$"))"
    $connection.Close()

    $xe_files = Get-Item "$($traceFileDirectory)system_health_*.xel"
    return $xe_files

}

# specify list of servers here
$serverList = @(
     "YourServer1"
    ,"YourServer2"
    ,"YourServer3"
)

try {
    foreach($server in $serverList) {

        $xe_files = Get-XeFiles -serverName $server
        foreach($xe_file in $xe_files) {
            try{
                # summary of events by event_name for each file
                $events = Read-SqlXEvent -FileName $xe_file.FullName
                Write-Host "Summary for file $($xe_file.FullName)"
                $events | Group-Object -Property Name -NoElement | Format-Table -AutoSize
            }
            catch {
                if(($_.Exception.GetType().Name -eq "AggregateException") -and ($_.Exception.InnerException -ne $null) -and ($_.Exception.InnerException.GetType().Name -eq "IOException")) {
                    # ignore error due to active trace file
                    Write-Host "$($_.Exception.InnerException.Message)"
                }
                else {
                    # rethrow other errors
                    throw
                }
            }
        }
    }

}
catch {
    throw
}