Identity Manager: Task Persistence Database Cleanup Using Stored Procedures

Products

CA Identity Manager CA Identity Governance CA Identity Portal CA Identity Suite

Issue/Introduction

Task Persistence tables can grow excessively large, which negatively impacts Identity Manager system performance Task Persistence Database and In Progress Tasks: Use, Performance and Troubleshooting. The standard Cleanup Submitted Tasks task in the User Console may be ineffective for massive data sets as it is designed for regular maintenance of a well-managed database Cleanup Submitted Tasks Not Working. In cases where millions of records exist, the task may hang or cause system instability Daily Stuck "Cleanup" Task Requiring IAM Restart.

Environment

Products: CA Identity Manager 14.x, 15.x; CA Identity Suite 14.x; IGA Xpress 1.x
Databases: MS SQL Server, Oracle
Deployment: On-Premise, Virtual Appliance (vApp)

Cause

Massive row accumulation in the tasksession12_5 and object12_5 tables typically occurs when regular cleanup is not scheduled or when high-volume bulk tasks are processed.

Resolution

For large-scale cleanup, use the built-in database stored procedures.

Warning: Perform a full Task Persistence database backup before executing these procedures Cleaning up Task Persistence DB of unnecessary data.

1. Identify the Correct Procedure

Select the procedure based on your archiving requirements Steps to perform IDM database cleanup:

garbageCollectTaskPersistence: Deletes tasks from the Task Persistence database without archiving them.
archiveTaskPersistence: Moves tasks from Task Persistence to the Archive database.
archive_gcTaskPersistence: Deletes old items from the Archive database.

2. Execute the Cleanup

Instructions and scripts are located in the following directories of the Identity Manager installation Cleanup Submitted Tasks Not Working:

SQL Server: ..\Identity Manager\tools\samples\TaskPersistence\SQLServer
Oracle: ..\Identity Manager\tools\samples\TaskPersistence\Oracle

Example Execution (SQL Server): The following example deletes completed tasks older than 12 months, limiting processing to 10,000 records per run to conserve database resources Cleanup Submitted Tasks Not Working.

sql

DECLARE @cutoff_time1 as datetime
SELECT @cutoff_time1 = dateadd(MONTH, -12, getdate()) 

EXECUTE garbageCollectTaskPersistence     
@ime_id = '1',     
@cutoff_time = @cutoff_time1,    
@task_limit = 10000,    
@totalTasks = ''

3. Key Parameters

@ime_id: The unique identifier for your Identity Manager Environment Cleanup Submitted Tasks Not Working.
@cutoff_time: Specifies the age of tasks to be cleaned. It is recommended to start by deleting only the oldest data (e.g., tasks older than 15 months) and ramping up gradually Cleanup Submitted Tasks Not Working.
@task_limit: Limits the number of tasks processed in a single execution. Use this to prevent database locks or performance degradation Cleanup Submitted Tasks Not Working.

You may also find more details about running Celanup submitted tasks storred procedure in documentation - Cleanup Submitted Task by Executing a Stored Procedure from a Database.

Additional Information

Cross-Database Scenarios: If your Task Persistence and Archive tables are on different database servers, specific cross-database scripts must be used Cleaning Archive and Task Persistence data in a Cross-Database scenario.
Stuck Tasks: Tasks in an "In-Progress" state are not removed by these procedures. If tasks are stuck due to system failure, they may require manual cancellation or resubmission using the Post-Events feature before they can be cleaned Task Persistence Database and In Progress Tasks: Use, Performance and Troubleshooting.