Delete Events and recover disk space from the Spectrum SRM DB

Products

• CA Spectrum
• DX NetOps

Issue/Introduction

We are running out of disk space and noticed the ddmdb event tables and SRM event tables are the cause.  We want to determine the top events & models which are responsible for this increase in DB size and then depending upon the results we want to perform cleanup of the ddmdb and SRM database.

DDMDB & SRM cleanup - how to manually delete events and recover disk space in Spectrum

Environment

All supported DX NetOps Spectrum releases

Resolution

Log into mysql - Navigate to

cd $SPECROOT/mysql/bin

For the Archive Manager (ddmdb):

Windows:

./mysql -uroot -proot ddmdb;

Linux:

./mysql --defaults-file=../my-spectrum.cnf -uroot -proot ddmdb;

For the reporting db:

Windows:

./mysql -uroot -proot reporting;

Linux:

./mysql --defaults-file=../my-spectrum.cnf -uroot -p<PASSWORD> reporting;

At the mysql prompt run the following query to see the top 10 events.  This query will take a long time to run on larger event databases:

select hex(type)

EventCode, count(*) EventCount from event group by EventCode order by

EventCount desc limit 10;

To delete events by event code, use this command - just change the event code:

delete from event where type=0x10d0c;

That will delete the event.

Now, in order to reclaim the space, you will need to optimize the event table. HOWEVER, take note, you MUST have 1.5x the amount of free space to optimize the table as mysql uses temporary storage to do this. So if your event table is 100GB, you need at least 150GB of FREE space

Assuming you have enough free disk space, you can then run:

optimize table event;

That will take quite some time. Once complete, type:

quit

Additional Information

***  Please Note that Optimize tables is still recommended to perform as part of the monthly maintenance even though Reporting database is now innodb database type ***

How to initialize SRM database: https://knowledge.broadcom.com/external/article?articleId=22484

Optmize MybSQL tables: https://knowledge.broadcom.com/external/article?articleId=38340

DDM queries: https://techdocs.broadcom.com/us/en/ca-enterprise-software/it-operations-management/spectrum/23-3/administrating/database-management/ddm-database-maintenance/ddm-database-queries.html