How to free up purged records in the Spectrum Report Manager (SRM) reporting database

Products

• CA Spectrum
• DX NetOps

Issue/Introduction

If the "Data Retention Policy" in the OneClick Administration -> Report Manager -> Preferences web page is set to "Purge", the old records (records that are older than the value configured in the "Data Retention Period (days) - Event Table" and "Data Retention Period (days) - Transformed Tables") are flagged for purge but they still take up space. 

This knowledge article explains how to recover that space to help control the size of the reporting database.

Environment

Release : Any version of Spectrum
Component : Spectrum Report Manager (SRM)

Resolution

Use mysqldump to backup the database and then restore the backup file to the reporting database. Records that are flagged to be purged are not save to the database backup file.

1. Log into the SRM system as the user that owns the Spectrum installation

2. Stop OneClick

3. If on Windows, start a bash shell by running "bash -login"

4. cd to the $SPECROOT/mysql/bin directory and enter the following command to backup the reporting database:

./mysqldump --defaults-file=../my-spectrum.cnf --routines --databases -uroot -<PASSWD> reporting > backup_filename.sql

5. Enter the following command to restore the backup file created in step 4 above:

./mysql --defaults-file=../my-spectrum.cnf -proot -u<PASSWD> < backup_filename.sql

6. Start tomcat as normal

Please reference the "Reporting Database Management" section of the Spectrum documentation for more information.

 

You can also optimize the tables in the reporting database using mysqlcheck with the "-o" option by doing the following:

1. Log into the SRM system as the user that owns the Spectrum installation

2. Stop OneClick

3. If on Windows, start a bash shell by running "bash -login"

4. cd to the $SPECROOT/mysql/bin directory and enter the following command to optimize the tables in the reporting database:

./mysqlcheck --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> -o reporting

5. Start tomcat as normal

Additional Information

Please note, depending on the size of the reporting database, the backup and optimize can take some time to complete.

Additionally, you will need to ensure you have enough disk space to perform either process. The general rule of thumb is you need at least the same amount of available disk space as the size of the reporting database. So if the reporting database is 10 GB, you need at least 10 GB of available disk space. The more the better.