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.
