Purging SQL Monitor data

SQL Monitor stores a large amount of high, medium and low volume data in the 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 that is older than the time specified for each category in your purging policy.

Configuring data purging

Go to the Configuration tab. Under Data Management, select Data purging:

ConfigPurging

Data is stored in three separate categories: Machine data, SQL Server data and Alert data. Each category is broken down further to make it easier to identify whether the data could become high, medium or low volume, and where the data is displayed in SQL Monitor:

Machine data:
- Basic machine data (low volume data) - displayed on the Host machine, Cluster, and SQL Server instance overview pages, and as metrics listed under Machine on the Analysis page.
- Windows process data (high volume data) - displayed as the System processes (top 10) on the Host or Cluster machine overview pages.
SQL Server data:
- Basic SQL Server data (low volume data) - displayed on the SQL Server instance overview pages, and as metrics listed under SQL Server on the Analysis page.
- SQL process data (high volume data) - displayed as SQL user processes (top 10 by CPU usage) on the SQL Server instance overview pages, and as alert details when Trace is turned off.
- Top 10 queries data (high volume data) - displayed as the Top 10 expensive queries on the SQL Server instance and Database overview pages.
- Database performance metric data (medium volume data) - displayed on the Database overview pages, and as metrics listed under Database on the Analysis page.
- Custom metric data (low volume) - data collected for custom metrics displayed on the Analysis page.
Alert data:
- Basic alert data (low volume data) - displayed on the Alert pages of raised alerts and including alert details, history of occurrences and performance data.
- Trace data (high volume data) - displayed under SQL Processes/Profiler trace in the Performance data section of the Alert pages when Trace is turned on.

The default purge settings ensure that all data older than 1 week is purged. If you would prefer high volume data to be purged more frequently than medium or low volume data, select a different time limit from the drop-down list for each category and click Save settings.

PurgingPolicy

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.

Note: There is a Do not purge option for each category, which means data will be stored indefinitely in your Data Repository. You should ensure that you have enough disk space available to accommodate potentially high volumes of data.

What is the growth rate of the SQL Monitor Data Repository?

You should expect the Data Repository database to grow by about 35 - 150 MB per day for each SQL Server instance you are monitoring. If you monitor 10 SQL Servers for a week, your database will increase by between 2 and 10 GB.

Note: The amount of growth will depend on how busy your monitored SQL Servers are, particularly the number of Windows processes, SQL processes and the number of databases on each instance.

What is the effect of purging data?

For overview data, a dash (--) or (no data) will be displayed on the overview pages next to a value if you rewind time to a point for which data does not exist because it has been purged.
For alert data, purging will result in older alerts no longer being displayed in the application.
For analysis graphs, a blank area will be displayed on the graph for the duration purged.

Was this article helpful?

Search support