IN_DB_UTIL_SP_CLEANUP_AUDIT_DATA - Perceptive Content Database IN_DB_UTIL Package 5.0 for SQL Server - Foundation 24.1 - Foundation 24.1 - Ready - Perceptive Content - external

The IN_DB_UTIL_SP_CLEANUP_AUDIT_DATA stored procedure can be used to cleanup audit data that is stored in the database. This procedure will delete data from the IN_AUDIT_DETAIL, and IN_AUDIT_OBJ, and IN_AUDIT table based on the value of the KEEP_DAYS parameter or the BEGIN_DATETIME and END_DATETIME parameters.

WARNING: The execution of this procedure to cleanup audit data could result in lock escalation and create contention on the audit tables that could impact the creation of new audit data and result in delays and errors during normal operations. It is advised to only execute this procedure after hours or on weekends while monitoring for database contention to ensure minimal impact to the Perceptive Content application. If necessary, utilize smaller batch sizes with the BATCH_SIZE parameter to help keep transaction sizes smaller and reduce the scope and duration of database locks.

Recommended index:

The following index is recommended to facilitate efficient searches using the CREATION_TIME column.

CREATE NONCLUSTERED INDEX AUDIT_IDX_01ON inuser.IN_AUDIT 
(     
   CREATION_TIME ASC,     
   AUDIT_ID ASC) 
WITH (DATA_COMPRESSION = PAGE) 
ON [SECONDARY]; 

Examples

Display Usage Information Only

EXEC [IN_CLEANUP_AUDIT_DATA] @HELP = 'YES' 

KEEP_DAYS Example:

The following will delete all audit data that is older than 365 days using batch sizes of 1,000 rows

Note that from a transaction standpoint the number of rows is based on qualifying rows in the IN_AUDIT table but the transaction also includes the deletion of rows from the IN_AUDIT_OBJ and IN_AUDIT_DETAIL tables which could have more or less than 1,000 each that correspond to the rows in the IN_AUDIT table. That means that each transaction will affect roughly three times the number of rows as specified in the BATCH_SIZE parameter. A BATCH_SIZE of 1,000 could result in a transaction size of roughly 3,000 or more rows. Beware of lock escalation that could result in table locks when using a batch size larger than 1,000 rows.

Note that in the examples below the Results tab will only contain the results of the final select statement displaying the RETURN_VAL of the procedure (0 = Success and 1 = Error). The output of the procedure, including number of rows counted or deleted, is displayed in the Messages tab.

DECLARE @return_value integer  

EXEC @return_value = IN_CLEANUP_AUDIT_DATA      
     @MODE            = 1     
    ,@KEEP_DAYS       = 365     
    ,@BEGIN_DATETIME  = NULL     
    ,@END_DATETIME    = NULL     
    ,@CONVERT_TO_UTC  = 1     
    ,@BATCH_SIZE      = 1000     
    ,@USE_TIME        = 0     
    ,@MAX_MINUTES     = 0     
    ,@DISPLAY_BATCHES = 'YES'     
    ,@INCLUDE_SUMMARY = 'NO'     
    ,@DEBUG           = 'NO' 
SELECT 'Return Value' = @return_value 
GO 

DATE RANGE Example:

The following example will delete all audit data that was created on January 1, 2018 between 8am and 5pm using a batch size 1,000 rows.

Note that to preserve the time part of the timestamps you must set USE_TIME = 1

Note that CONVERT_TO_UTC = 1 (default) which means the provided timestamps will be converted to the UTC timestamp so that it aligns with the timestamps in the database.

DECLARE @return_value integer  

EXEC @return_value = IN_CLEANUP_AUDIT_DATA      
     @MODE            = 1     
    ,@KEEP_DAYS       = NULL     
    ,@BEGIN_DATETIME  = 'January 01 2018 8:00 AM'     
    ,@END_DATETIME    = 'January 01 2018 5:00 PM'     
    ,@CONVERT_TO_UTC  = 1     
    ,@BATCH_SIZE      = 1000     
    ,@USE_TIME        = 1     
    ,@MAX_MINUTES     = 0     
    ,@DISPLAY_BATCHES = 'YES'     
    ,@INCLUDE_SUMMARY = 'YES'     
    ,@DEBUG           = 'NO'  

SELECT 'Return Value' = @return_value 
GO