How does event purging maintenance work in Notification Server 6.0 SP3?

book

Article ID: 180922

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Question
What steps are performed in SQL during the Notification Server Event purging process?

Answer

All of the event tables (EVT_*) are analyzed at least once, if not twice, every night using scheduled tasks. The scheduled tasks execute the SQL procedure spPurgeResourceEvent. The list of known purging tasks below come from article 31754:

  • NS.Purging.Maintenance.{de4a3a7c..9b}
  • NS.Hidden Purging Maintenance.{16290a7d..ec}

With SP3, the procedure was rewritten to “nibble” away at the event tables, in attempt to avoid the prior problem where the 10 minute database timeout would cause event table row deletions to be rolled back (resulting in very large tables over time). Several event tables, prior to SP2, were not managed via the purging process, resulting in unconstrained growth. This did not completely cure the problem, as the new approach is still wrapped in a transaction, and can also rollback.

The purging procedure currently works like this:

A cursor is defined that points to the contents of vEventClass. The important columns are GUID, DataTableName, PurgeData, PurgeMaxRows, and PurgeKeepDays.The cursor loops through each of the event tables.

Part 1: Purge by Time Constraint

If purging is enabled (PurgeData=1), then it builds and executes a query that deletes up to 10,000 rows which exceed the cutoff time (Today > PurgeKeepDays) for the current table. This part of the purge process repeats until there are no additional rows over the allowed time constraint for the target table.

Part 2: Purge by Max Rows

Next (still working with the same table), the MaxRowPurge setting is checked. If enabled, a temporary table is created. A query is built and executed to copy the RowID and EventTimes for the most recent X (MaxRowPurge) rows from the target event table into the temp table.

Another query is then built to delete up to 10,000 rows from the event class table, avoiding any of the rows that were referenced in the temporary table. This deletion step repeats until there are no additional rows with an EventTime older than the oldest row from the temporary table.

The cursor then moves on to the next event table.

The signature of any SQL error encountered during the procedure begins with "sp_PurgeResourceEvents: Critical error".