Identity Manager 14.4
If you have any BulkTask that you have completed (state = 128) but you have tasks included inside of BulkTask as Modify User that are in progress (state=2048), Cleanup Submitted Tasks will not be able to clean and may still crash the cleaning process of subsequent tasks in the list you want to delete.
1. Do a query to identify the Period that is concerned that tasks are not being deleted by Cleanup Submitted tasks:
e.g. Tasks for the period that are not being deleted, for example, 2021-08-01 to 2021-09-01:
SELECT * FROM [<Your_Database>].dbo.[tasksession12_5] where (state=128 or state=256 or state=512 or state=32 or state=2 or state=1048576) and created_time between '2021-08-01' and 2021-09-01';
Let's say find about 579 tasks that should have been selected to delete by the cleanup submitted tasks but for some reason are not being deleted.
You can try to delete the task if you know its tasksessionid using the StoredProcedure garbageCollectByTaskid.
2. Create a list of tasksessionid of these tasks and executed in each one the stored procedure garbageCollectByTaskId: e.g.
(Warning: when running this command, the Task will be deleted from Identity Manager database and there is no way to go back if you don't have a Database backup)
EXEC garbageCollectByTaskId 'taskid'
Example: EXEC garbageCollectByTaskId '1914772a-de674f61-31d07a18-397284f'
Example: EXEC garbageCollectByTaskId('1914772a-de674f61-31d07a18-397284f');
3. Check that all tasks on the list have been deleted.
If any task is not deleted, try to individually rerun the stored procedure garbageCollectByTaskId <taskid>
If the stored procedure gets stuck and doesn't show any results, it may indicate a problem in the specific task that needs to be analyzed.
4. Check if the task that crashed is a BulkTask type Task.
5. It was selected to be deleted because it is in state 128. But if any of the Included tasks are in Progress (example state=2048) the deletion of the main task will hang in
garbageCollectByTaskId stored procedure.
So the stored procedure garbageCollectByTaskId never returns an answer when running over these tasks in this state.
6. Thus, identify if any task included in the bulk tasks that were completed (state=128), is not in progress (2048).
An example: Bulk task showing completed and a modified user task inside the bulk task showing in progress.
This can crash the process of the cleanup submitted task.
7. Manually change the status of this modified user task that is subordinate to the bulk task from in progress (2048) to canceled (256).
eg. Let's stay the tasksessionid of Modify User (In Progress Task) that is included in BulkTask that is completed is Your_tasksessionid
UPDATE [<Your_Database>].[dbo].[tasksession12_5] set state=256 where tasksessionid='Your_tasksessionid'
8. After this ran again over the Bulk Task the garbageCollectByTaskId over the tasksession id of Bulk Task, and at this time didn't hang anymore.
9. Repeat this if find other BulkTask hanging to cleanup.
Please, always ask your DBA for help to do any maintenance on your Database.
Always make a backup of your Database and a Snapshot of your Vapp or on-premises servers before making any changes.
If you need to rollback the backups will be the way to get back to the original situation before you do any changes to the environment.
The garbageCollectByTaskId StoredProcedure removes data from the database without asking. Without backup, there is no way to return the data.