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 SQLcommands:

   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).