SQL Server system_health Session Retention
March 3, 2017
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 the number of maximum rollover files 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 the information you are looking for is not available. I will explain how you can solve this problem and retain system_health session files for a 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 the 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.