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?
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.
Release: SSADSC99000-4.2-Service Operations Insight-Data Source Capacity
- 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.