Purging aom2 database tables for VAIM (Maintenance)

Products

• CA Virtual Assurance for IM

Issue/Introduction

• Purging aom2 database tables for VAIM (Maintenance)

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.

Environment

Release : 12.9

Component : VPM GENERAL

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