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. 

Undo with Database Snapshots

How to undo the data changes using Database Snapshots

In this blog post, I would like to talk about how can we revert or undo the data changes which might have happened by a user mistake. For example, if some one has deleted a useful data from the database (for which there is already a snapshot exists prior to the data deletion), we can get the data back using the database snapshots. In this post, I am not going to talk about how the database snapshots works and it's internals.

To demo this, let me create a database and create a table and populate with some data. To do this, you can use the script below.

CREATE DATABASE VSNPSHT
GO
USE VSNPSHT
GO
CREATE TABLE VSample (id int, name char(10))
GO

So, we have the table created now. Let me populate it with some data. To do this, just execute the below T-SQL Statement.

INSERT INTO VSample VALUES (1, 'V')
GO 100

Let me now create a Database Snapshot for this database.

CREATE DATABASE VSNPSHT_SNAPSHOT ON
( NAME = VSNPSHT, FILENAME = 'C:\MSSQL\DATA\VSNPSHT_DB_SNAPSHOT.ss')
AS SNAPSHOT OF VSNPSHT
GO

Here, the NAME refers to the logical file name of the data file, and the filename is used to indicate the location for the snapshot file to be created. We can view the snapshot of this database under the path Databases -> Database Snapshots in SQL Server Management studio as shown below.

To access the data from the snapshot, you need to use the database name in the query before the table or the schema name as shown below.

select * from [VSNPSHT_SNAPSHOT].[dbo].[VSample]

Alternatively, you can select the database context to the Snapshot in the QueryAnalyser to select the data from the snapshot.

Now, let me delete the data from the table in the database.

Delete from [VSNPSHT].[dbo].[VSample]

Now, to get the data back to the database using the snapshot, execute the T-SQL Statement below. This statement looks similar to a restore statement.

USE master;
RESTORE DATABASE [VSNPSHT] FROM DATABASE_SNAPSHOT = 'VSNPSHT_SNAPSHOT';
GO

We all are set. Now we should get the data back and you can verify it through a select statement on the table. But, keep in mind that this option would bring the database back to the state when the snapshot was created. So, if you are looking for any specific table's data to be recovered, this option may not work as described here. You need to do some more operations for that like export the data from the snapshot table to a temp table and then import the desired data to the live table.

Hope this helps you.. Thanks for reading.