Steps to Recover the system_health Extended Events Session in SQL Server

By: Manvendra Singh   |   Updated: 2015-06-23   |   Comments (3)   |   Related: > Extended Events

Problem

In SQL Server there is a default Extended Events session named system_health to help with troubleshooting problems. This session records various important and critical events. Microsoft recommends that we should not stop or delete this session. Suppose this session was deleted by mistake, this tip will help you to recover the system_health Extended Event on your SQL Server instance.

Solution

As per MSDN, Extended Events are a light weight performance monitoring system that uses very few resources. The system_health session is one of the default Extended Events sessions.  This session starts automatically when the SQL Server database engine starts, and runs without any noticeable performance impacts. The session collects system data that we can use to help troubleshoot performance issues in the database engine. Therefore, Microsoft recommends that we should not stop or delete the session because the information captured by this session can be used to find the cause of certain problems.

Steps to Recover the system_health Extended Events Session in SQL Server

Step 1: Launch SQL Server Management Studio, connect to the SQL Server instance, expand the Management folder. You will find the Extended Events options there, expand this option to see all the Extended Events sessions running on that SQL Server instance. We can see two Extended Events sessions are showing in the below screenshot, one of them is system_health.

Step 2: We can see the Extended Events running on the SQL Server instance. If you want to see live data which is captured under this Extended Event session, we can see that by selecting system_health session then right click to select the "Watch Live Data" option. Once you click on that, another window with Live Data values appears in the right side pane. Below is the screenshot of Live Data on my SQL Server instance.

Step 3: This data is very helpful in case of troubleshooting an issue. That is why Microsoft recommends neither stopping nor deleting this session. Let's delete this session manually so that we can show you how to restore this session. Run the code below to drop the system_health Extended Events session.

-- Delete system_health extended event.
DROP EVENT SESSION system_health
ON SERVER

Step 4: Once the code above is executed, the target Extended Event system_health session will be deleted and disappear from the SQL Server instance. Below is a screenshot of the Extended Events on this SQL Server instance after system_health session is deleted. We can see only one Extended Event session in system table as well as in SSMS. Run the code below to check all Extended Events for the SQL Server instance.

-- Check extended events.
SELECT * FROM sys.server_event_sessions

Step 5: We can restore the deleted XE session by executing a T-SQL script called "u_tables.sql" file in SSMS. This script ships with SQL Server and is located on the drive where we installed the SQL Server program files. My SQL Server program files are installed on the C drive so this script is located at "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Install".

We can see the script in the above screenshot. Now our next step is to run this script on our SQL Server instance.

Step 6: Open this script in a new query window and execute it against your SQL Server instance. I did the same and received the below output after running this query.

Step 7: Once the above script is executed successfully on your SQL Server instance, you can check your Extended Events section in SSMS to verify whether the system_health session has been created or not. We can do the same exercise as we did in step 4 to check the newly created session. Run the below code or expand the Management folder of SSMS.

-- Check extended events.
SELECT * FROM sys.server_event_sessions

We can see system_health session is created on the SQL Server instance.

Step 8: Our next step to start this newly created session by using the ALTER EVENT SESSION statement or by using the Extended Events node in Object Explorer to make sure it started running and is capturing the required data.

--Make sure to start the session if it's not started.
ALTER EVENT SESSION system_health
ON SERVER
STATE = start;
GO

Since this session is already running, it throws this message.  Otherwise above script will start running successfully.

Next Steps

• Never stop or delete these Extended Events on your SQL Server instances. Extended Events can offer valuable help in troubleshooting and diagnosing any issue on the SQL Server.
• Learn more:
  • SQL Server Database Administration Tips
  • SQL Server Extended Events Tips

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips