You are using an unsupported browser. Please update your browser to the latest version on or before July 31, 2020.
close

Looking for the Diagnostic Utility?

Click Here For Download and Usage Instructions

Archiving EventsGeneral Table

Problem

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

Solution

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

How to archive EventsGeneral

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

 

  1. Make a backup copy of the pstar database before continuing.
  2. 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.
  3. Use the 'create_archive_table.sql' script to create a new table named EventsGeneral_archive in the archive database. This new table is based off the EventsGeneral table, but will not contain an identity column (which makes inserts much easier).
  4. Use the 'create_archive_storedproc.sql' script to create a new stored procedure in the new archive database. It will batch process the records in the EventsGeneral table, only keeping the last “N” days’ worth of records. Older rows will be moved to the EventsGeneral_archive table in the “pstar_archive” database. This stored proc uses a batch limit of 4000 to prevent against exclusive table locking.
  5. The archive can be initialized manually with the following SQL commands:
    • exec pstar_archive.dbo.archiveEventsGeneral [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 EventsGeneral and EventsGeneral_archive tables should be checked to ensure the data is transferring between the two as expected:
    • exec pstar_archive.dbo.archiveEventsGeneral 1095
    • exec pstar_archive.dbo.archiveEventsGeneral 975
    • exec pstar_archive.dbo.archiveEventsGeneral 855
    • exec pstar_archive.dbo.archiveEventsGeneral 735
    • exec pstar_archive.dbo.archiveEventsGeneral 615
    • exec pstar_archive.dbo.archiveEventsGeneral 495
    • exec pstar_archive.dbo.archiveEventsGeneral 375
    • exec pstar_archive.dbo.archiveEventsGeneral 255
    • exec pstar_archive.dbo.archiveEventsGeneral 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. 10/2021 | PortalGuard

  • 157
  • 14-Oct-2021
  • 252 Views