ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.
Purging aom2 database tables for VAIM (Maintenance)
Article ID: 186100
Updated On:
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.
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
*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
Attachments
Feedback
Yes
No
Powered by
