SQL Server system_health Session Retention

As per Microsoft’s books online

The system_health session is an Extended Events session that is included by default with SQL Server. This session starts automatically when the SQL Server Database Engine starts, and runs without any noticeable performance effects. The session collects system data that you can use to help troubleshoot performance issues in the Database Engine. Therefore, we recommend that you do not stop or delete the session.

What this article does not tell you is your individual file size is 5 MB and number of maximum rollover file is 4. Meaning you will only get 20 MB of data. Once that limit is reached your older data is lost and there is no way to recover.

Often time you have an issue with the application pointing to SQL Server and you want to diagnose the problem with system_health files. You find that files already rolled over and information you are looking for is not available. I will explain how you can solve this problem and retain system_health session files for longer period.

Check which extended event sessions are configured and running in your SQL Server.

SELECT 
	name,
	event_retention_mode_desc,
	max_dispatch_latency,
	max_memory,
	track_causality,
	isRunning=
	CASE startup_state
	WHEN 0 THEN 'No'
	WHEN 1 THEN 'Yes'
	END
FROM 
	sys.server_event_sessions
GO

Check what is your current retention and location of files. Size is in Megabyte. If you do not see a file location, it means you are writing in your default error log folder.

Find current retention.

SELECT 
	es.name, 
	esf.name, 
	esf.value 
FROM  
	sys.server_event_session_fields AS esf
JOIN 
	sys.server_event_sessions AS es
ON 
	esf.event_session_id=es.event_session_id
WHERE es.startup_state=1
	AND (esf.name= 'filename' 
		OR esf.name ='max_file_size' 
		OR esf.name='max_rollover_files')

Check SQL server error log default location. You can find this in many ways but I found the best method here.

SELECT 
	is_enabled,
	[path]
FROM
	SYS.dm_os_server_diagnostics_log_configurations
GO

Check if you have enough space in your destination location in order to increase retention. Based on the current retention you need to make decisions about the size of individual files and how many you want to keep. This is a generic script and you will have to change some of the values (where I have comments) based on your need.

ALTER EVENT SESSION [system_health] 
ON SERVER STATE = STOP
GO
ALTER EVENT SESSION [system_health] 
ON SERVER DROP TARGET package0.event_file 
ALTER EVENT SESSION [system_health] 
ON SERVER ADD TARGET package0.event_file 
	(SET FILENAME=N'system_health.xel',--name of the session
	max_file_size=(25), --size of each file in MB
	max_rollover_files=(40)) --how many files you want to keep
GO
ALTER EVENT SESSION [system_health] 
ON SERVER STATE = START
GO

Hope you find it useful.

FacebookTwitterGoogle+Share