Unable to cleanup Task Persistence Database as per 'Taskpersistence cleanup_update4.docx' document

Products

• CA Identity Manager

Issue/Introduction

One of the usual reasons Identity Manager tasks become stuck in progress is large number of rows in the tables of the Task Persistence Database (TP DB).

This community post: Understand your persisted data stuck in an asynchronous state. discusses the ways to cleanup the TP DB.

The 'Taskpersistence cleanup_update4.docx' document (it is attached to the above mentioned community post) describes procedures that were developed to assist when the number of rows in the TP DB tables are of such a huge number that it is difficult for the existing OOTB IM clean up task to complete in a reasonable amount of time in a production environment.

However, a customer reported SQL error when trying to run this SQL statement:

INSERT INTO archive_object12_5 SELECT * FROM object12_5;

Error message:

Msg 1204, Level 19, State 4, Line 7

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

The customer utilized MS SQL server to hold TP DB.

Environment

Release : 14.2

Component : IdentityMinder(Identity Manager)

Cause

THe number of rows is so large SQL server has problem cloning TP DB table in one go.

Resolution

Our recommendation is to try table level locking using tabloc or tablocx instead of row level locking:

INSERT INTO archive_object12_5 SELECT * FROM object12_5 WITH (TABLOCKX);

If the above approach does not help the way to do would be to copy by chunks (maybe together with table level locks).

This particular table -  object12_5 - is most likely to cause this locking problem, and it doesn't provide a lot of opportunities to use in where clause.

objecttype field in object12_5 table has values 1 and 2.

objectid field has values like this:
dc22264e-3f57ffe1-4fbb0f0e-ba04011b
i.e. it's 4 hex integers separated by hashes.

So this kind of SQL statement can be tried:

INSERT INTO archive_object12_5 SELECT * FROM object12_5 WHERE objectid like 'a*' AND objecttype='2';

You'd need to use all values from 1* to f* in combination with object type 1 and 2 in where clause.