000029239 - How to Manage Database Logs

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 4Show Document
  • View in full screen mode

Article Content

Article Number000029239
Applies ToIdentity Management and Governance -  all versions
IssueThe table T_AV_JOB_STATS is used by jobs running in the system to log the activity that they are performing. This table is typically used by processes or jobs that are executing SQL commands as a way to log their activity or process steps.  The contents of this table can be usually seen as the "Log Files" in the Run Details of jobs.
User-added image

Typically this information is used for diagnosing functional or performance problems, therefore the records in this table do not need to be maintained forever, but can be pruned from the table based on their creation date after a period of time. If the records are not pruned from this table it can become extremely large. The rate at which it will grow is based on a number of factors. Typically the biggest contributors to the size of this table are Collection Runs and Review Generations. So if executed frequently the table can grow rapidly and make the Admin Monitoring page difficult to access as well as affect backup/restore operations.

An administrator can purge this table by 2 mechanisms:

First is through the "Admin->Monitoring" page seen here:
User-added image

In the monitoring page the most recent job to be deleted is selected.  The "Action" button is selected and the option "Delete selected run and older" is chosen.  This will remove the selected job and all jobs that are older.  When this option is used the rows will be removed and the existing space consumed by the table can be reused for other rows.  If the space used by the table needs to be released for other tables to use then the following commands will need to be run in the database as AVUSER during a time of low activity.

Alter table t_av_job_stats enable row movement;

Alter table t_av_job_stats shrink space;

Alter table t_av_job_stats disable row movement;

Another way to remove the rows is through a PLSQL package:

Data_Retention_Pkg.Prune_Run_Details (in_older_than IN DATE, in_shrink_space IN BOOLEAN DEFAULT TRUE)

in_older_than - This represents the date at which the Pruning should take place.  All records that were created on this date and later will be deleted.

in_shrink_space - This is a flag which will be used to determine if the space used by the deleted rows are released to the database for other table's use.  If this option is used then the procedure needs to be executed during a time of low activity.
User-added image

NotesNote that deleting database logs via the UI method deletes all associated run data from the system.  Using the Data_Retention_Pkg.Prune_Run_Details technique only deletes the detailed log data but leaves the run data intact.  I.e. you would still be able to see that a particular collection ran on a specific date but would no longer see the detailed steps that occurred during the run.