Purging aom2 database tables for VAIM (Maintenance)

Products

• CA Virtual Assurance for IM

Issue/Introduction

• Purging aom2 database tables for VAIM (Maintenance)

Environment

Release : 12.9

Component : VPM GENERAL

Cause

• Over time _CA_AuditEntry, _CA_ProcessIndicationEx and _CA_ProcessIndication_keys tables can grow very large in size.
• If these tables contain millions of rows worth of information they can be safely truncated to reclaim disk space.

Resolution

1. Stop CAAIPApache and CAAIPTomcat Services.

2. Run the following SQL Query

use aom2

truncate table _CA_ProcessIndicationEx
truncate table _CA_ProcessIndication_keys
truncate table _CA_AuditEntry



3. Start CAAIPApache and CAAIPTomcat Services.

Additional Information

The following SQL Query will show what tables in aom2 are using the most data:

*DO NOT DELETE ANY TABLE THAT ARE NOT REFERENCED UNDER THE SOLUTION FIELD.


use aom2
Declare @PageSize as int
Select @PageSize= low/1024.0 from master.dbo.spt_values Where Number=1 And type='E'
select object_name(i.object_id) as [Table Name]
, Convert(numeric(18,3),Convert(numeric,@PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 
THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) / 1024) 
As [Data Space Used (In Mbs)]

,Convert(numeric(18,3), Convert(numeric(18,3),@PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)) / 1024)  
As [Index Space Used  (In Mbs)]
, SUM(Case When p.index_id=1 and a.type=1 Then p.rows else 0 end) 
As [Total No of Rows]

FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
LEFT Join sys.tables t ON i.object_id=t.object_id 
 
Where t.type='U'
Group By object_name(i.object_id)
order by [Data Space Used (In Mbs)] desc