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

book

Article ID: 185494

calendar_today

Updated On:

Products

CA Spectrum

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.

Resolution

Log into mysql - Navigate to $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 -proot 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

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/10-3-2/administrating/database-management/ddm-database-maintenance/ddm-database-queries.html