How to purge older data from AuditRecords in SOI Db
search cancel

How to purge older data from AuditRecords in SOI Db


Article ID: 190716


Updated On:


CA Service Operations Insight (SOI)


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?


Release : 4.2

Component : Service Operations Insight (SOI) Manager


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