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.

5 replies on “SQL Server system_health Session Retention”

This has helped me, I wanted to review 2 weeks older deadlock information and couldn’t found I have increased the number and size of files.

But still I’m not sure whether it will capture only last 24 hours or more?

Thanks,
Hitesh

Hi Hitesh,
I am glad that you found it useful. It will depend on server activity. There is no way to set the retention based on date and time.

Hi ! I’m hitting this error (when I’m trying to “View target data” of the event) after I changed the file size & rollover file of the system health extended events. And the ‘filename’ is actually missing when I query from the sys.server_event_session_fields. Any idea?

TITLE: Microsoft SQL Server Management Studio
——————————

The storage failed to initialize using the provided parameters. (Microsoft.SqlServer.XEventStorage)

——————————
ADDITIONAL INFORMATION:

A severe error occurred on the current command. The results, if any, should be discarded.
A damaged buffer was found in file “D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\ERRORLOG” at offset 0, no further events will be read from the file. (Microsoft SQL Server, Error: 0)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3281&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

——————————
BUTTONS:

OK
——————————

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.