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

Archive EventsSSO Table

Problem

The EventsSSO table is becoming very large, causing certain reports to run slowly or performance loss.

Solution

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

How to archive EventsSSO

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_table_EventsSSO_archive.sql' script to create a new table named "EventsSSO_archive" in the archive database. This new table is based off the EventsSSO table, but will not contain an identity column (which makes inserts much easier).
  4. Use the 'create_proc_archiveEventsSSO.sql' script to create a new stored procedure in the new archive database. It will batch process the records in the EventsSSO table, only keeping the last “N” days’ worth of records. Older rows will be moved to the EventsSSO_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.archiveEventsSSO [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 EventsSSO and EventsSSO_archive tables should be checked to ensure the data is transferring between the two as expected:
    • exec pstar_archive.dbo.archiveEventsSSO 1095
    • exec pstar_archive.dbo.archiveEventsSSO 975
    • exec pstar_archive.dbo.archiveEventsSSO 855
    • exec pstar_archive.dbo.archiveEventsSSO 735
    • exec pstar_archive.dbo.archiveEventsSSO 615
    • exec pstar_archive.dbo.archiveEventsSSO 495
    • exec pstar_archive.dbo.archiveEventsSSO 375
    • exec pstar_archive.dbo.archiveEventsSSO 255
    • exec pstar_archive.dbo.archiveEventsSSO 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

  • 160
  • 21-Oct-2021
  • 265 Views