Cleanup Submitted Tasks Not Working; Database stored procedure for cleaning up task persistence is available.
Article ID: 43199
Updated On:
Products
Issue/Introduction
Tasks Persistence tables have become large, which negatively impacts system performance.
The Cleanup Submitted Tasks task may have no effect in cleaning up the tables because it is not intended to cleanup massive numbers of rows, rather it is designed to maintain weekly or daily cleanups of an already well maintained database.
Environment
CA Identity Manager 14.x, 15.x
CA Identity Suite 14.x
IGA Xpress 1.x
Resolution
For cleaning up large numbers of completed tasks use the built-in database stored procedure.
By default, the instructions for running the stored procedure are located in the readme.txt here:
..\CA\Identity Manager\IAM Suite\Identity Manager\tools\samples\TaskPersistence\SQLServer
..\CA\Identity Manager\IAM Suite\Identity Manager\tools\samples\TaskPersistence\Oracle
For Virtual Appliance 14.4.x:
Use the script exec_sqlserver_gc.sql to run the stored procedure.
The example in the readme.txt is...
select @cutoff_time1 = dateadd(minute, -5, getdate())
This will delete everything but the most recent 5 minutes of data.
The main idea is to use the SQL dateadd command which has many options available.
You'll probably want to delete only the oldest week or month to start and ramp up once you're comfortable with the number of rows deleted and the time required.
If you have task persistence data that is 16 months, you might start by deleting everything older than 15 months. This would look something like this...
dateadd(month, -15, getdate())
For example,
@cutoff_time1 as datetime
select @cutoff_time1 = dateadd(MONTH, -15, getdate())
EXECUTE garbageCollectTaskPersistence
@ime_id = ''1",
@cutoff_time = @cutoff_time1,
If you do want to take advantage of these two new required parameters:
Task limit is a number that would limit the number of tasks that would get archived. It's useful mainly when you don't know the volume of tasks that qualify (which at times can be millions), and you want to limit the amount of resources the DB needs to complete the procedure. For example, 10000, meaning after it cleans up 10k tasks it would stop even though there are more qualified transactions under your criteria.
The total tasks parameter is an output parameter - when a task gets archived it increases this by one and when it's completed you'd get Total Tasks output saying how many transactions got cleaned up.
For example, the following clears up all completed tasks older than 12 months, but will stop after processing 10,000 records
@cutoff_time1 as datetime
select @cutoff_time1 = dateadd(MONTH, -12, getdate())
EXECUTE garbageCollectTaskPersistence
@ime_id = ''1",
@cutoff_time = @cutoff_time1,
@task_limit = 10000,
@totalTasks = ''
You would need to run this multiple times to take care of the tasks in the backlog, but if the paramethers will be set correctly so storred procedure will not cause errors in the DB and will run in short period of time it should make backlog manageable.
If after you run this query to a point where it shows 0 tasks removed and you still have tasks that should be removed but aren't please reach out to support.
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
Here's one of many links to the dateadd syntax, but please consult with your Database Administrator to verify the syntax.
NOTE: The Archive and the Task Persistence tables must reside within the same database server for tasks to be properly archived when task cleanup is performed. See Separate Database Configuration (Optional) for more details.
Feedback
