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:

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.
• 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. 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. For more information, see Growth in size of Data Repository database.

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.