Non-Recuring completed maintenance schedules from prior to CU6 are not being pruned from UIM on schedule

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

Non-Recuring completed maintenance schedules from prior to DX UIM 23.4 CU6 (and later) are no longer pruned from UIM, causing them to remain visible in the Operator Console.

Environment

• Version: 23.4 CU6 and higher
• Database: MS SQL Server, Oracle, or MySQL

Cause

Starting with DX UIM 23.4 CU6, a change was introduced in how maintenance schedules are managed to support new archiving features. Legacy maintenance schedules created prior to the CU6 upgrade may have a NULL value in the DELETE_SCHEDULE field of the MAINTENANCE_SCHEDULE database table. Because this field is NULL, the maintenance_mode probe does not recognize them as candidates for automatic deletion or archiving, leaving them in a "limbo" state.

Resolution

(It is recommended to take a backup of the of Maintenance_schedule and Maintenance_schedule_members tables before performing the operation)

To resolve this issue, you must manually trigger the deletion/archiving process for the affected legacy schedules.

Step 1: Identify Stuck Schedules

Run the following query against your UIM database to identify non-recurring schedules that are missing the deletion flag:

sql

SELECT SCHEDULE_ID FROM MAINTENANCE_SCHEDULE WHERE RECURRENCE_PATTERN = 0 AND DELETE_SCHEDULE IS NULL;

 

Step 2: Manually Delete Schedules via Probe Utility

For each SCHEDULE_ID returned in Step 1, use the pu (probe utility) command to manually archive or delete the schedule. Replace the placeholders with your environment's details:

from the nimsoft/bin directory

pu -u [administrator_user] -p [password] /[domain]/[hub]/[robot]/maintenance_mode delete_schedule [SCHEDULE_ID]

Note: You can automate this for multiple IDs using a batch script or loop.

looping example

 for %i in (1 2 3 4 5 6) do pu -u [administrator_user] -p [password] /[domain]/[hub]/[robot]/maintenance_mode delete_schedule %i

 

Additional Information

Purging data via database query:

 

Before deleting, it is safer to first verify the count:

SELECT COUNT(*) FROM MAINTENANCE_SCHEDULE WHERE RECURRENCE_PATTERN = 0 AND DELETE_SCHEDULE IS NULL;

For safer batch deletion (recommended for large tables), you can delete in chunks as below:

For MSSQL Server:

DELETE TOP (1000) FROM MAINTENANCE_SCHEDULE WHERE RECURRENCE_PATTERN = 0 AND DELETE_SCHEDULE IS NULL;

Perform the count query to verify that all records are removed. Once the count is 0, perform the following:

DELETE TOP (1000) 
FROM MAINTENANCE_WINDOW_HISTORY 
WHERE SCHEDULE_ID NOT IN (SELECT SCHEDULE_ID FROM MAINTENANCE_SCHEDULE)
AND SCHEDULE_ID NOT IN (SELECT SCHEDULE_ID FROM MAINTENANCE_SCHEDULE_ARCHIVE);

Perform count check to verify records are cleaned:

SELECT COUNT(*)
FROM MAINTENANCE_WINDOW_HISTORY 
WHERE SCHEDULE_ID NOT IN (SELECT SCHEDULE_ID FROM MAINTENANCE_SCHEDULE)
AND SCHEDULE_ID NOT IN (SELECT SCHEDULE_ID FROM MAINTENANCE_SCHEDULE_ARCHIVE);

 

For Oracle:

DELETE FROM MAINTENANCE_SCHEDULE WHERE ROWID IN (SELECT ROWID FROM MAINTENANCE_SCHEDULE WHERE RECURRENCE_PATTERN = 0 AND DELETE_SCHEDULE IS NULL FETCH FIRST 1000 ROWS ONLY);

Perform the count query to verify that all records are removed. Once the count is 0, perform the following:

DELETE FROM MAINTENANCE_WINDOW_HISTORY  WHERE ROWID IN ( SELECT ROWID FROM MAINTENANCE_WINDOW_HISTORY  WHERE SCHEDULE_ID NOT IN (SELECT SCHEDULE_ID FROM MAINTENANCE_SCHEDULE) AND SCHEDULE_ID NOT IN (SELECT SCHEDULE_ID FROM MAINTENANCE_SCHEDULE_ARCHIVE) AND ROWNUM <= 1000 );

Perform count check to verify records are cleaned:

SELECT COUNT(*) FROM MAINTENANCE_WINDOW_HISTORY  WHERE SCHEDULE_ID NOT IN (SELECT SCHEDULE_ID FROM MAINTENANCE_SCHEDULE) AND SCHEDULE_ID NOT IN (SELECT SCHEDULE_ID FROM MAINTENANCE_SCHEDULE_ARCHIVE);

After Deleting the schedules, it is recommended to Rebuild the Indexes of below tables:


Index Rebuild (for MSSQL Database):

ALTER INDEX ALL ON maintenance_schedule REBUILD WITH (ONLINE = ON);

ALTER INDEX ALL ON maintenance_schedule_members REBUILD WITH (ONLINE = ON);

ALTER INDEX ALL ON maintenance_window REBUILD WITH (ONLINE = ON);

Index Rebuild (for Oracle Database):

SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD;' FROM USER_INDEXES WHERE TABLE_NAME = 'MAINTENANCE_SCHEDULE';

SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD;' FROM USER_INDEXES WHERE TABLE_NAME = 'MAINTENANCE_SCHEDULE_MEMBERS';

SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD;' FROM USER_INDEXES WHERE TABLE_NAME = 'MAINTENANCE_WINDOW';