Purging SQL Monitor data
Published 08 July 2015
SQL Monitor stores a large amount of data in its Data Repository. To prevent the Data Repository database using all your hard disk space, SQL Monitor checks for old data every hour and purges data older than the time specified for each category.
Configuring data purging
Go to the Configuration tab. Under Data management, select Data purging:
Data is stored in two categories: data you’ll want to view trends for, and performance troubleshooting data. You can choose how long to keep various types of data in the Data Repository. We recommend storing data that you'll want to view trends for over time for longer than performance troubleshooting data, which you're more likely to need only in the short term.
The default purge setting for each type of data is displayed in brackets:
- Data you'll want to view trends for
- Alert data (2 months) - Displayed in various sections of the Alert details pages, including Details, Alert history, Occurrences and Performance data.
- Basic machine data (2 months) - Displayed on the Host machine, Cluster, and SQL Server instance overview pages, and on the Analysis page, listed under Machine metrics.
- Basic SQL Server data (2 months) - Displayed on the Availability group, SQL Server instance, and Database overview pages, and on the Analysis page, listed under SQL Server metrics and Availability group metrics.
- Custom metrics (2 months) - Displayed on the Analysis page, listed under Custom metrics.
- Database performance counter data (1 week) - Displayed on the Database overview page, and on the Analysis page, listed under Database metrics.
- Performance troubleshooting data
- SQL process data (2 weeks) - Displayed on the SQL Server instance overview page under SQL user processes (top 10 by CPU usage), and on the Alert details pages under Details (when trace is turned off).
- Performance diagnostics data (1 week) - Displayed on the SQL Server instance and Database overview pages under Top waits and Top queries, and on the Alert details pages under Performance data.
- Windows process data (1 week) - Displayed on the Host and Cluster machine overview pages under System processes (top 10), and on the Alert details pages under System processes.
- SQL trace data (3 days) - Displayed on the Alert details pages, in the Performance data section, under SQL processes/Profiler trace (when trace is turned on).
The default purge settings are designed to keep the data you're most interested in for longer without using up too much disk space. To change these settings, select a different time limit from the drop-down list for each category and click Save settings.
How frequently should I purge data?
Your purging frequency depends on:
- how important it is for you to retain historic information about each category. For example, for auditing purposes, you may need to keep data about your machines for longer than alert data.
- the amount of disk space you have available in your Data Repository to store collected data. If space is an issue, you may consider purging high volume data on a more frequent basis.
There is a Do not purge option for each category, which means data will be stored indefinitely in your Data Repository, but you should ensure that you have enough disk space available to accommodate potentially high volumes of data.
What is the effect of purging data?
- For overview data, a dash ( - ) will be displayed on the overview pages next to a value if you rewind to a point in time for which data doesn't exist because it's been purged.
For alert data, purging will mean older alerts are no longer displayed in the application.
For analysis data, the graph will display a blank area for the duration purged.
Reducing data retention
If you decide to reduce the amount of data being retained, for example reducing Basic Machine Data from 2 months to 2 weeks, then the purge process will require time to process the data. Therefore say that there is 5GB of Basic Machine Data, and a change should reduce that to 3GB, the process needs to identify and delete 2GB of data.