IN_DB_UTIL_SP_CLEANUP_WF_ARCH_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_WF_ARCH_DATA stored procedure can be used to cleanup archived workflow data. This procedure will delete data from the IN_WF_ITEM_ARCH and IN_WF_ITEM_HIST_ARCH and IN_WF_ITEM_QUEUE_HIST_ARCH tables 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 archived workflow data could result in lock escalation and create contention on the affected tables which could 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.

When running this procedure you have the option to specify whether or not to delete archived workflow data for content that still exists (Folders and Documents). By default, this option (HAS_CONTENT) is set to 0 (NO) which restricts the procedure from deleting archived workflow data for folders and documents that still exist. To override this behavior, you can execute the procedure with HAS_CONTENT = 1 (YES) to indicate that you want to delete all qualifying archived workflow items regardless of whether there is referencing content that may still exist.

Index Considerations

The following index change can be considered to facilitate improved index usage when using the default option of HAS_CONTENT = 0. Estimated improvement is 11.6%.

CREATE NONCLUSTERED INDEX [WF_ITEM_ARCH_IDX03]   
  ON [inuser].[IN_WF_ITEM_ARCH]([ARCHIVE_TIME])   
  INCLUDE ([OBJ_ID])   
  WITH (DROP_EXISTING = ON, ONLINE = ON, DATA_COMPRESSION = PAGE)   
  ON [SECONDARY]; 

Examples

Display Usage Information Only

EXEC [IN_CLEANUP_WF_ARCH_DATA] @HELP = 'YES' 
GO 

KEEP_DAYS Example:

The following will delete all archived workflow 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_WF_ITEM_ARCH table but the transaction also includes the deletion of rows from the IN_WF_ITEM_HIST_ARCH and IN_WF_ITEM_QUEUE_HIST_ARCH tables which will likely have more than 1,000 rows that correspond to the rows in the IN_WF_ITEM_ARCH table. That means that each transaction will affect significantly more rows than specified in the BATCH_SIZE parameter so beware of lock escalation that could result in table locks when using larger batch sizes.

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_WF_ARCH_DATA      
     @MODE            = 1     
    ,@HAS_CONTENT     = 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'     
    ,@DISPLAY_ONLY    = 'NO'     
    ,@DEBUG           = 'NO'  

SELECT 'Return Value' = @return_value 
GO 

DATE RANGE Example:

The following example will delete archived workflow data that does not still reference existing content that was created on January 1, 2018 between 8am and 5pm using a batch size of 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_WF_ARCH_DATA      
     @MODE            = 1     
    ,@HAS_CONTENT     = 0     
    ,@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'     
    ,@DISPLAY_ONLY    = 'NO'     
    ,@DEBUG           = 'NO'  

SELECT 'Return Value' = @return_value 
GO