Skip links

Identity Manager: DB Task Cleanup Failure – MS SQL Server

Some of our customers have very active Identity Manager (IdM) environments which process thousands of tasks per hour. Identity Manager provides a stored procedure for task cleanup, but depending on the DB size and available temp space this task would fail and the database would continue to grow. The stored procedure can cause the temporary table to become exhausted and the task never completes. If you are using the bulk scheduler for task cleanup, you may never see the failure.

We wanted to share an MS SQL script that will reduce the IdM DB size. After its execution, the cleanup task will be able to run normally. The script performs the following actions for Identity Manager 14.x:

  • Removes the foreign keys
  • Truncates the task persistent related tables
  • Recreates the foreign keys

Note: Have your MS SQL Server DBA validate this script before executing. Also, all Identity Manager environments should be stopped before executing this script. Backup your IdM environments in the IdM Management Console using the export option.

Step 1: Remove Foreign Keys

Removing the foreign keys allows the tables to be truncated in the second step.

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_tmp_Bulkloader12_5_tasksession12_5') AND parent_object_id = OBJECT_ID(N'dbo.tmp_Bulkloader12_5'))
ALTER TABLE dbo.tmp_Bulkloader12_5 DROP CONSTRAINT FK_tmp_Bulkloader12_5_tasksession12_5
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_event_object12_5_event12_5') AND parent_object_id = OBJECT_ID(N'dbo.event_object12_5'))
ALTER TABLE dbo.event_object12_5 DROP CONSTRAINT FK_event_object12_5_event12_5
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_event12_5_tasksession12_5') AND parent_object_id = OBJECT_ID(N'dbo.event12_5'))
ALTER TABLE dbo.event12_5 DROP CONSTRAINT FK_event12_5_tasksession12_5
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_tasksession12_5_tasksession12_5') AND parent_object_id = OBJECT_ID(N'dbo.tasksession12_5'))
ALTER TABLE dbo.tasksession12_5 DROP CONSTRAINT FK_tasksession12_5_tasksession12_5

Step 2: Truncate IdM Tables

TRUNCATE TABLE dbo.tmp_Bulkloader12_5
TRUNCATE table dbo.event_object12_5
TRUNCATE table dbo.event12_5
TRUNCATE TABLE dbo.tasksession12_5
TRUNCATE TABLE dbo.lock12_5

Step 3: Recreate Foreign Keys

ALTER TABLE dbo.event_object12_5  WITH CHECK ADD  CONSTRAINT FK_event_object12_5_event12_5 FOREIGN KEY(eventid) REFERENCES dbo.event12_5 (eventid)
ALTER TABLE dbo.event_object12_5 CHECK CONSTRAINT FK_event_object12_5_event12_5
ALTER TABLE dbo.event12_5  WITH CHECK ADD  CONSTRAINT FK_event12_5_tasksession12_5 FOREIGN KEY(tasksessionid) REFERENCES dbo.tasksession12_5 (tasksessionid)
ALTER TABLE dbo.event12_5 CHECK CONSTRAINT FK_event12_5_tasksession12_5
ALTER TABLE dbo.tasksession12_5  WITH CHECK ADD  CONSTRAINT FK_tasksession12_5_tasksession12_5 FOREIGN KEY(initiatorid) REFERENCES dbo.tasksession12_5 (tasksessionid)
ALTER TABLE dbo.tasksession12_5 CHECK CONSTRAINT FK_tasksession12_5_tasksession12_5
ALTER TABLE dbo.tmp_Bulkloader12_5  WITH CHECK ADD  CONSTRAINT FK_tmp_Bulkloader12_5_tasksession12_5 FOREIGN KEY(tasksessionid) REFERENCES dbo.tasksession12_5 (tasksessionid)
ALTER TABLE dbo.tmp_Bulkloader12_5 CHECK CONSTRAINT FK_tmp_Bulkloader12_5_tasksession12_5

After executing the script and restarting IdM, verify all of the schedule tasks are present. Be sure to schedule the cleanup task to run daily.

We hope this brief post will help you manage your Identity Manager environment. If you need assistance with your Identity Manager solution or another IAM product, please reach out to SIS.


If you want to know our recent offer please subscribe to our newsletter