Soitoolbox takes long time to purge data

book

Article ID: 129125

calendar_today

Updated On:

Products

CA Service Operations Insight (SOI)

Issue/Introduction

We ran soitoolbox to purge historical and cleared alert data from the DB. We ran command on our test machine and it took 2 hours to clean around 2.8 but whn same command was run on out production SOI manager it took more than 8 hour to clean around 1.1 million records. What caused this delay and if there are ways to purge the data faster?

Cause

Here are few things that can cause this kind of delay in purging data:
- DB index file can get fragmented causing performance issue.
- The distance from your SOI manager to your DB, network connection between them and network latency can also cause slowness 
- Resource crunch (memory or CPU) on the SQL server can also cause delay. 
- Your production SOI server is more active than your test SOI server in accessing/inserting/updating/deleting data to and from the DB, so if you run the soitoolbox while SOI service are running then it'll take       long time.
 

Environment

Release: SSADSC99000-4.2-Service Operations Insight-Data Source Capacity
Component:

Resolution

- You can run the attached cleanHistory.sql script to purge data for the DB history tables. Open the script with an editor to see what tables are affected. You need to copy the content of the script and run it           from SQL query in SQL Management Studio. 
- To purge old cleared alerts run the content from attached purgeClearedAlerts_Full.sql.
- Before running the above script's edit them and  change the value for " SET @ArchiveAge" to 90 days, this'll delete data older than 90 days.
- Also attached is a script "rebuildIndexes.sql", running this'll rebuild the indexes.
  
 

Attachments

1558537110430SQL_spripts.zip get_app