EnterWorks - ETT 149 - How to Clean Up History Records for Scheduled Job Repositories Using SQL

Every time repository records are modified or deleted, new history records are created of the previous record in the B_MASTER_REPOSITORY_ITEM table.  The history records provide the details of the differences between versions of the record.  For most repositories, the rate of change is low enough that the list of versions for the record will be relatively low.  For scheduled jobs, repository records may be created or updated every minute.  Attempting to view the recent history of records in these repositories may fail or take a long time to display in the UI if they process frequently. 

The Server Properties cleanup can be configured to remove older history records, but this setting applies to all repositories.  If there are business requirements to keep the history records for business data for a long period or forever means the processing-related repositories will continue use a large amount of database space and make it difficult or impossible to view the recent history records for them.

The SQL Server stored procedure EW_PurgeHistoryRecords has been created to selectively purge older history records from designated repositories.  This allows the history for rapidly changing repository records to be trimmed of their older history records while leaving the history records for other repositories intact.

This session shows how to use the SQL stored procedure.

Prerequisites - ETT 066, ETT 067, ETT 068