Scripts to purge historical data

Document created by jaisa05 Employee on Apr 28, 2016Last modified by jaisa05 Employee on Jul 5, 2017
Version 3Show Document
  • View in full screen mode

Introduction:

The maintenance activity is an essential activity to be considered in environment. This document has been written to share the purge script along with purging document to outline how to use the scripts. It is recommended to purge the old historical data (no more useful) from data base which can impact the performance due to increase size of objects in database.

 

The tables being purged are the tables containing event information and temporary auditing information. The document includes a high level overview of the Database schema for information.

 

Environments:

  • Applicable for Release Automation 5.x - 6.x
  • Database: Microsoft SQL, Oracle DB, MySQL

 

Instructions:

The scripts included in supplied zip file provide a mechanism to purge data from the Release Automation database. The two stored procedures are provided for purging data in the Release Automation Database:

  1. The first script is used for purging offline execution Jobs
  2. The second script can be used to purge audit records.

 

Attachment Map

Database Type
Attachment Name
Microsoft SQL DatabaseSQLBestPracticeGuide-2-0.zip
Oracle DatabaseOracleBestPracticeGuide-2-0.zip
MySQL DatabaseMySQLBestPracticeGuide-2-0.zip

 

Additional Information:

Recommendations:

  1. It is recommended that the stored procedures be executed during off hours though a scheduled job.
  2. The two stored procedures can be executed concurrently in different database sessions.
  3. However, do not execute the same stored procedure more than once simultaneously
  4. Read the guide enclosed in each zip providing an overview of

 

 

Added improvised script sp_purge_execution_jobs-mysql-v1.1.sql (Only for MySQL it is improvised in terms of performance on large data set)

Outcomes