DX NetOps Manager

 View Only

Script for cleaning SRM databases 

Aug 01, 2018 11:47 AM

Over the years, we have had many issues with Spectrum Report Manager (SRM).  Some of the most frustrating issues have been around stability due to the massive amount of data that we process.  What we have found is that shortly after we wipe the SRM systems out, they run well for a while, but then start experiencing issues again.  After looking at our SRM issues, we decided to try to setup an automated process that would attempt to clean the MySQL tables that are the biggest problems that we have with stability.  Especially the Events table.  In the process, we created this simple little script.  We have now been running this script for several months, and our SRM issues have disappeared.  What we do is to schedule the script to run once a week.

Please note, there are several things to be aware of.

    1 - While this has been working well in our environment, it may not in yours.  Be careful.  Maybe try it on a development system first.  No guarantees are made.  This script is provided to help others, but we will not maintain it.

    2 - The disk space that is required to optimize the table is 1.5 times the table size for each table that is optimized.  This is extremely important.  Make sure you have enough space, especially for the event table.


I hope you find this helpful.

0 Favorited
0 Files

Tags and Keywords


Nov 27, 2019 11:08 AM

Fun and useful to share, but I cannot find any file attached. Where is the script?

Aug 02, 2018 02:30 PM

Not completely, no.

If you want to disable SRM completely, there are other steps you can take but those steps involve restarting tomcat…


In most cases, you will be fine with it running.  MySQL should be able to handle table and row locking while the optimize is running.

Aug 02, 2018 01:48 PM

Thank you Matt. If we just disable the monitor under Report Manager --> Spectrum Status, will it suffice (it will prevent data transaction from DDMDB to Reporting DB)?

Aug 02, 2018 01:25 PM

Hey Rajashekar,


Yes - it is suggested you shutdown Tomcat.  This will prevent any/all write attempts to the database and prevent data loss.

If it is your primary OneClick as well, then you can leave it running but we do suggest stopping it.



Aug 02, 2018 12:00 PM

Hi Matt,


While optimizing the tables or deleting the events from event table, do we really need to stop the tomcat?


Thank you.


Aug 01, 2018 11:59 AM

Hey Bill,


Thanks for sharing this script.

Your site has always been the outlier in terms of SRM data, and volume in general


We should note that the CA Archiver can work for most customers who are looking to purge data older than X days.

The archiver runs every midnight on the SRM host.


Reviewing the script, the most important piece of the puzzle is the optimize being done.

This is very important and CA suggests doing it at LEAST once a month, better off doing it more frequently like Bill is here.

We should also note that while an optimize is running SRM will not be able to:


  1. Run Reports
  2. Process new data


That being said, you will also not lose any data as it will be there in the DDMDB.


I love seeing this type of sharing amongst our customers! 



Related Entries and Links

No Related Resource entered.