Release Automation

Scripts to purge historical data 

04-28-2016 05:33 AM

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)

Statistics
0 Favorited
12 Views
4 Files
0 Shares
12 Downloads
Attachment(s)
zip file
SQLBestPracticeGuide-2-0.zip   767K   1 version
Uploaded - 05-29-2019
zip file
OracleBestPracticeGuide-2-0.zip   683K   1 version
Uploaded - 05-29-2019
zip file
MySQLBestPracticeGuide-2-0.zip   53K   1 version
Uploaded - 05-29-2019
zip file
sp_purge_execution_jobs-mysql-v1.1.sql.zip   1K   1 version
Uploaded - 05-29-2019

Tags and Keywords

Comments

11-20-2019 03:41 AM

The sp_purge_audit gives an error on MS SQL Server (12.0.5659.1)
Msg 240, Level 16, State 1, Procedure sp_purge_audit, Line 42 [Batch Start Line 36]
Types don't match between the anchor and the recursive part in column "foreign_key_name" of recursive query "t1".

Related Entries and Links

No Related Resource entered.