Subscribe to PortalGuard's Quarterly Newsletter for News & Updates on the Latest Release! Click to Subscribe

Archiving RBAEvents Table

Problem

The pstar SQL Database is too large to run Reports and most of the storage is being taken up by the RBAEvents table.

Solution

Run SQL scripts to back up data for a set number of days and Archive the RBAEvents table to lighten up the production database.

How to archive RBAEvents

NOTE: These steps utilize two (2) SQL scripts in the attached zip file.

    1. Create a new database and name it “pstar_archive”. Since it’s secondary in nature, the files for this new archive database can be created on a different disk if desired.
    2. Create a new table named RBAEvents_archive in the archive database. It is based off the RBAEvents table, but will not contain an identity column (which makes inserts much easier). Please use the create_archive_table.sql script to do this.
    3. Create a new stored procedure in the new archive database. It will batch process the records in the RBAEvents table, only keeping the last “N” days’ worth of records. Older rows will be moved to the RBAEvents_archive table in the “pstar_archive” database. This stored proc uses a batch limit of 4000 to prevent against exclusive table locking. Please use the create_archive_storedproc.sql script to create this stored proc.
    4. The archive can initialized manually with the following commands:
      • exec pstar_archive.dbo.archiveRBAEvents [number of days to retain]

NOTE: If there is a large backlog of data, the run can take many hours to move the data. To ensure correct operation, the stored procedure should be run manually using a very high number of days initially (e.g. 1095 would leave 3 years’ of data). This number can be reduced until the target retention (e.g. 90) is reached. For example, the following manual runs should be performed. After each run, the RBAEvents and RBAEvents_archive tables should be checked to ensure the data is transferring between the two as expected:

  • exec pstar_archive.dbo.archiveRBAEvents 1095
  • exec pstar_archive.dbo.archiveRBAEvents 975
  • exec pstar_archive.dbo.archiveRBAEvents 855
  • exec pstar_archive.dbo.archiveRBAEvents 735
  • exec pstar_archive.dbo.archiveRBAEvents 615
  • exec pstar_archive.dbo.archiveRBAEvents 495
  • exec pstar_archive.dbo.archiveRBAEvents 375
  • exec pstar_archive.dbo.archiveRBAEvents 255
  • exec pstar_archive.dbo.archiveRBAEvents 135

Once caught up with any backlog of data, the stored procedure should be scheduled to run weekly during a low usage time (e.g. very early Sunday morning). The argument to the script should be the decided upon “active” retention period (e.g. 90 days).

REV. 04/2018 | PortalGuard