Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Steps to Recover the system_health Extended Events Session in SQL Server


By:   |   Read Comments (3)   |   Related Tips: More > 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.

system_health extended events session

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.

Watch live data of the xe session system_health

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

Delete xe system_health

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

3655_check_xe_sessions after deletion

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

u_table.sql script location

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.

u_table script execution

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

Check system_health session creation

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

Start the system_health session

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

Next Steps


Last Update:






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

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, August 11, 2016 - 11:53:24 AM - Konstantin Back To Top

The only article around the web how to recreate the system health + is the only way to do it without cousing massive blockings due to the fact script is droping the event and then is starting it instead for stop and then start.

 

Manvendra is our DBA hero :)


Wednesday, July 01, 2015 - 1:01:19 PM - Gourang Back To Top

Very useful tip saved my day :)


Tuesday, June 23, 2015 - 7:23:24 PM - Saurav Vaish Back To Top

Another good one from you Manvinder Sir. Thanks. This was helpful.


Learn more about SQL Server tools