How to purge older data from AuditRecords in SOI Db

Products

• CA Service Operations Insight (SOI)

Issue/Introduction

We found 133 million records in AuditRecords in SAMStore Database, due to this high number of records, SOI console doesn't show Audit Records for CIs & services
How to delete older records from this table?

Environment

Release : 4.2

Component : Service Operations Insight (SOI) Manager

Resolution

Please follow this procedure to delete older records from this table

1. select count(*) from AuditRecordActions
      (note down the count)

2. select * into AuditRecords_temp from AuditRecords
      (this will move all records from AuditRecords to temp table)

3. truncate table AuditRecords
       (truncate all records from this table, this will be faster as this table has too many records)

4. insert into AuditRecords(ID,Type, TypeDetail, InternalID, TenantID, Action, ActionDetail, Component, UserName, TimeStamp)
    select * from AuditRecords_temp where TimeStamp > '2020-05-01'

    Note: You may change the above where condition value if required, the above query will copy records > 1st May 2020

5. select * from AuditRecords
      (this is to confirm to see it has records after 1st May 2020)

Note: It is recommend to run these queries while SOI manager is shutdown also recommend to try this in test environment first