What is database maintenance and how do I use it?

Last week, the Head Geek and I did a webinar on Orion NPM product training.  Once of the common things that kept coming up was questions regarding database maintenance.  What is it?  How do I know if it is working right?  How do I customize it?  So I figured what better place to expand on this topic than the Orion product blog.

First off, what is it?

The formal definition:
Database maintenance performs a series of data summarizations that help you optimize the size of your Orion database. Data summarization consists of gathering all the collected network data for a defined period of time, calculating statistics from the data, and then discarding the data itself while retaining the statistics. In addition to data summarizations, it also cleans up data related to deleted items in the database, saving additional space.  Orion automatically runs database maintenance every night keep your database compact and performing well.

What does this mean?
Within Orion there are tons of dials and knobs you can turn to tweak your installation of Orion including how long we keep data for before purging and how we summarize data.

For example, in the Admin section of the web console if you are on NPM 9.5 you can go to Polling Settings and under Database Setting you can find some of these items including when the scheduled nightly database maintenance job will run (default is 2:15am).

This brings up a question around what exactly does data summarization mean and how does it affect Orion? There are three retention options to discuss.

• Detailed Statistics Retention
  All statistics in the Orion database collected at any frequency shorter than 1/hour are summarized into hourly statistics after the period of time designated as the Detailed Statistics Retention period. By default, this period is 7 days.
• Hourly Statistics Retention
  All statistics in the Orion database that are recorded at any frequency shorter than 1/day but longer than 1/hour are summarized into daily statistics after the period of time designated as the Hourly Statistics Retention period. By default, this period is 30 days.
• Daily Statistics Retention
  All statistics in the Orion database older than the Daily Statistics Retention period are deleted. By default, this period is 365 days.

With the default settings, reports covering the last week will have detailed data. Reports covering the last month will have hourly data. You can run reports with daily data covering up to the last year. Beyond a year there is no data.

How do if I know if the job is running correctly each night?

On the Orion server if you navigate to the installed directory, mine is C:\Program Files\SolarWinds\Orion, you will find a set of files in there named swdebugMaintenance.log and 5 more with a .number extension on the end.  Open this up and you will see what the job did, how long it took to run and if there were any errors.  If you don’t see at the end that the  maintenance has completed, if there are any errors or if it is taking a very long time to complete, you may want to further investigate to see if something is wrong.