Ttdfjt

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

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

Hat tip to Erik Darling for suggesting the query hints, which sped things up significantly in my tests

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 Group-Object -Property Name -NoElement 
 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 FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLLogsystem_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 FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLLogsystem_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 "\servernamed$")
 $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 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