SQL Server DataStore historical data purge script

Summary

This article provides and describes a script that is designed to purge all historical performance data from the up.time DataStore based on a cutoff date that is entered in the script. This cutoff date can be a static date or a rolling date that allows the script to be run on a recurring basis to perform regular incremental data purges.

This script is designed for a Microsoft SQL Server 2005/2008 DataStore running with an up.time 5.x monitoring station.

It is highly recommended that you review the script and are comfortable with SQL scripting before choosing to use this utility.

NOTE: THIS SCRIPT IS PROVIDED AS IS WITH NO WARRANTY OR GUARANTEE. DELETE OPERATIONS ARE FINAL. IT IS HIGHLY RECOMMENDED THAT YOU COMPLETE A DATASTORE BACKUP BEFORE USING THIS SCRIPT.

Usage

The following variables must be updated after downloading and loading the attached script into your SQL Server Management Studio:

@CUTOFF_DATE: This variable is set to today - 180 days by default. You can adjust '180' to reflect the number of days of data you wish to retain in your DB, or set the variable to a static date. All data found to be older than the @CUTOFF_DATE will be permanently deleted from the DataStore.

@DELETE_DATA: This variable is set to 0 by default, which will allow the script to execute and provide a summary of data deletion without actually deleting the data. The output should be verified and, if it is correct, set @DELETE_DATA = 1 and rerun the script to complete deletion of your historical data.

Once you have verified that the script runs correctly, you have the option of creating a SQL Server scheduled job that will re-execute the purge on a scheduled basis. For example, by using a rolling @CUTOFF_DATE of 90 days and configuring SQL Server to execute the script weekly or monthly, you will ensure that your DataStore only contains 90 days of data on a regular basis.

NOTE: The up.time Data Collector or uptime_core service must be stopped while this script is executed to ensure data consistency and integrity.