Orion Alert History Table Retention (Post NPM 11.5) (SOLVED)

EDIT (See Below for the Delete Query I Ran Manually): Thanks to ctlswadmin's prompting, I checked my nightly maintenance log (swdebugmaintenance.log) and sure enough, the maintenance was timing out each night when it came time to work on the AlertHistory table. I manually deleted all alerts older than 90 days from that table where their AlertActiveID was not in the AlertActive table and then manually ran a DB maintenance from the Orion server. All's good now. Thanks for all of the responses and help.

As confirmed by LadaVarga's response here, there is no way to set a retention period for the Alert History table. And I specifically mean the table in the Orion database called AlertHistory, not the Active Alerts table in the web console. We have a ton of alerts and our Alert History table is HUGE. I can query it and get results from as long ago as 12/24/2015, which is a lot more than the 60 days our "Event Retention" setting is set at. I bring up the Event Retention because in LadaVarga's reply I linked to she mentions that the AlertHistory table uses the Event Retention time, which doesn't seem to be true since our Events table only has the last 60 days worth of events whereas our AlertHistory table has 9 months of events. I also find that 9 months is a pretty odd number of days since I don't see any setting in our Polling Settings where we have 270 days set as the retention period...

I see a Stored Procedure in the Orion database called dbm_AlertHistory_DeleteStale, but what process within the Orion software uses this procedure and how can we decide the datetime value to feed into this? Because of the AlertHistory table's size, a lot of our Orion web console views that display historical alert information take a really long time to load. Our SWISv3 logs are fully of "Query Took a Long Time to Execute" warnings and nearly every one of those queries causing the warning are against the AlertHistory table.

Is it safe for me to copy/paste the SQL from this stored procedure and manually run it with the datetime variable defined to a value of my choosing? I'm thinking anything older than 90 days needs to go. What effect will this have on the Alert Details views since they give a lot of historical info about the alerts, like how many times a particular object has triggered this alert for example?

Here is what I ran manually to get my AlertHistory table to a smaller size so that the nightly maintenance could start taking over again and not timing out on this table. You can change the number of days to match what your Event Retention period is in your Polling Settings:

BEGIN  SET NOCOUNT ON;  DECLARE @EventHistoryRetention as int  DECLARE @ChunkSize as int  /* This number should be equal or greater than your Event Retention setting in Settings > Polling Settings */  SET @EventHistoryRetention = 90  /* How many records to delete at a time.  I recommend leaving this at 1,000 */  SET @ChunkSize = 1000  SET IMPLICIT_TRANSACTIONS OFF  SET ROWCOUNT @ChunkSize NextChunk:  DELETE FROM   /* You can comment out the "DELETE FROM" line above and uncomment the "SELECT * FROM" line below to see the  first chunk that it would delete.  Good for verifying before running. */  --SELECT * FROM  AlertHistory Where [TimeStamp] < DATEADD(DAY,-@ROWCOUNT = @ChunkSize GOTO NextChunkEND

Find more posts tagged with

alerthistory

database size

data retention

Accepted answers

ctlswadmin

I would imagine that there is a retention period for this table, I looked all over and couldn't find anything. I also looked at a few of my db's and none of them have old data like yours. Have you checked if your SW daily maintenance is completing successfully, I might try running that independently and seeing if that fixes anything. The way I would approach this issue is via deleting alerts older than a specific amount of days. If you're looking to clear alerts older than 90 I would create a job that runs once a day to delete those records.

All comments

xtraspecialj

Bumping this to try and get some visibility, especially a SolarWinds guru like LadaVarga, aLTeReGo, Leon Adeto, or tdanner that can possibly answer this.

ctlswadmin

xtraspecialj

Thank you for the response. Yes, our maintenance is running successfully every day. I have a log parser monitor that searches it for errors and warnings each day and a file age monitor that alerts when older than 24 hours. I'll go through the log and see if I see anything funny, although I know there isn't a warning or error message in the log because my parser monitor says there isn't. Ok, nevermind. My log parser clearly says there are warning and error messages in the log. I was looking at the wrong part of the monitor. And sure enough, the errors and warnings revolve around the alert history tables. I get several warnings like:

LONG RUNNING QUERY: 1278679.5761 msLONG RUNNING QUERY: 100587.2197 msLONG RUNNING QUERY: 3600513.3262 ms

And then an error where SWISv3 did not receive a reply within the configured timeout.

Good call ctlswadmin! Gonna manually delete the Alert History log older than 90 days and see if the maintenance can run after that.

LadaVarga

Hello,

It will never delete data from alerthistory if the record from Alerthistory has counterpart in ActiveAlert tables. . That means if you have many active alerts, records from Alerthistory table will be protected.

You should have look to swdebugmaintenance.log and check

2016-09-21 02:15:05,329 [1] INFO SolarWinds.Data.DatabaseMaintenance.MaintenanceEngine - Beginning Maintenance for AlertHistory

*** Assembly SolarWinds.Orion.Swis.Client, Version=10001.1.400.33, Culture=neutral, PublicKeyToken=null, .NET version v4.0.30319 ***

*** Assembly SolarWinds.InformationService.Contract2, Version=2016.3.0.24, Culture=neutral, PublicKeyToken=null, .NET version v4.0.30319 ***

2016-09-21 02:15:06,095 [1] INFO SolarWinds.Data.DatabaseMaintenance.MaintenanceEngine - Removing orphaned data from AlertHistory

2016-09-21 02:15:06,110 [1] INFO SolarWinds.Data.DatabaseMaintenance.MaintenanceEngine - Removing older data from AlertHistory

2016-09-21 02:15:06,142 [1] INFO SolarWinds.Data.DatabaseMaintenance.MaintenanceEngine - Finalizing maintenance for AlertHistory

It is sharing retention with Events table.

LAda

tdanner

It should be trimming anything in AlertHistory older than the Event retention time, except for history records associated with alerts that are *still active*. Some troubleshooting steps:

* Check that those old AlertHistory records are not associated with active alerts - that they are actually eligible to be dropped.

* Check the database maintenance log for errors. It's at C:\ProgramData\SolarWinds\Logs\Orion\swdebugMaintenance.log. Make sure the timestamps are current!

* Crank up the log level for database maintenance to DEBUG (you can use the Log Adjuster tool for this), run Database Maintenance manually (it's on the start menu), check the log for interesting tidbits (search for "AlertHistory") and then put the log level back to INFO

* Still no joy? Call support.

edit, later: I should have checked the thread to see if someone had already replied!

xtraspecialj

So you are saying if I do a DELETE in SQL itself that the records won't be deleted because of their relationship to the other table? Or, do you just mean that the Orion maintenance process deletes them in a way that protects them? I put a WHERE clause in my SQL DELETE statement to protect any Active Alert records, so not a big deal. Just curious if I needed that.