System_Health XE Logging

Changing the default output log location for the System_Health eXtended Event in SQL Server 2012

Starting from SQL Server 2012, a new feature is introduced to track and record the SQL Server Health condition which is nothing but Server_Health extended event. Extended Events is a new feature introduced with SQL Server 2008 which provides the operational information of SQL Server and light weight tool to track the events that are happening in the SQL Server. 

SQL Server 2012 Management studio provide a nice way of creating or operating the extended events through the GUI as shown below.

Extended events in SQL Server 2012 Management Studio

As you can see there, 3 XEvents are created and AlwaysON_health and System_health are the default. The system_health XEvent keeps track of the system health in the output files in the location of the SQL Server errorlog by default. Some times, we may need to change the location of the output that this Xevent generates to a different location. This may be the case if you want to maintain the history for a longer period. As this is the default XEvent gets created by SQL Server, we have no initial setting to change it. 

So, here is the script to change the log file path to any desired one. 

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'C:\Program Files\Microsoft SQL Server\system_health.xel')
GO 

Note: change the path in the filename to any desired one you need.

This script first deletes the existing value of the log location and creates a new target with the new location.