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: https://community.broadcom.com/communities/community-home/digestviewer/viewthread?MID=813007#bm10e756df-3144-423e-a158-12312b5c4c62 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;
The customer utilized MS SQL server to hold TP DB.
THe number of rows is so large SQL server has problem cloning TP DB table in one go.
Release : 14.2
Component : IdentityMinder(Identity Manager)
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:
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.