Maximo Database Table Data Purging

Maximo Database Table Data Purging involves removing unused data, indexes, and attachments that may slow down database read operations and consume unnecessary storage. 

Performing regular data purging helps improve database performance and reduce the cost of owning the cloud database service for clients.

Some of the cleanup activities can be performed include:

• Always take a Database backup before running purge scripts, especially in Production environment

• Removing non-critical business data from out-of-the-box Maximo tables 

      Certain Maximo tables store operational or historical data that may no longer be required after a period of time. for example, CRONTASKHISTORY, ESCREPEATTRACK etc., Below are some sample scripts that can be used to purge old records.

-- delete crontask history records that are more than 2 years old based on start time
delete from crontaskhistory where starttime < dateadd(YEAR,-2,GETDATE());

-- delete escalation repeat tracking records that have records in historyflag
delete from escrepeattrack where escalation='POCLOSE' and ownerid in (select poid from po where historyflag=1);
delete from escrepeattrack where escalation='SRCLOSED' and ownerid in (select ticketuid from sr where historyflag=1);
delete from escrepeattrack where escalation='ESCBLTNEXP' and ownerid in (select BULLETINBOARDuid from BULLETINBOARD where expiredate < dateadd(DAY,-1,GETDATE()));
delete from escrepeattrack where escalation in ('WOCAN','WOCLOSE') and ownerid in (select workorderid from workorder where historyflag = 1);
delete from escrepeattrack where escalation in (select escalation from escalation where active = 0);

-- delete communication log (includes escalation) that are more than 8 years old based on created date
delete from commlog where createdate < dateadd (YEAR,-8,GETDATE());

• Delete child records first before deleting the parent records; otherwise, it may result in orphan records in the database.

-- delete Message Tracking records that are more than 3 months old based on initial date time
delete from maxintmsgtrkdtl where  meamsgid in (select meamsgid from MAXINTMSGTRK where initialdatetime < dateadd(DAY,-90,GETDATE()));
delete from maxintmsgtrk where initialdatetime < dateadd(DAY,-90,GETDATE());   

-- delete Integration Error Messages that are more than 2 years old based on statusdate
delete from maxinterrormsg where messageid in (select messageid from maxinterror where statusdate < dateadd(YEAR,-2,GETDATE()));
delete from maxinterror where  statusdate < dateadd(YEAR,-2,GETDATE());

• Batch Deletions 

        When deleting a large volume of records from a table, it's recommended to perform the operation in batches to avoid the transaction log becoming full due to active transactions.

-- SQL Server Script
SET NOCOUNT ON;
 
    DECLARE @DeletedRows INT = 0;
    DECLARE @TotalDeleted BIGINT = 0;
    DECLARE @BatchStart DATETIME;
    DECLARE @StartTime DATETIME = GETDATE();
 
    PRINT CONCAT('Cleanup started at ', CONVERT(VARCHAR(19), @StartTime, 120), '.');
 
    WHILE 1 = 1
    BEGIN
        SET @BatchStart = GETDATE();
 
       delete top (50000) from escrepeattrack 
       where escalation='WOCLOSE' and objectname = 'WORKORDER' 
       and not exists (select 1 from workorder (nolock) 
       where escrepeattrack.ownerid = workorderid); 
 
        SET @DeletedRows = @@ROWCOUNT;
        SET @TotalDeleted += @DeletedRows;
        
        PRINT CONCAT('Deleted ', @DeletedRows, ' rows in this batch (started at ', 
                     CONVERT(VARCHAR(19), @BatchStart, 120), 
                     '). Total deleted so far: ', @TotalDeleted, '.');
         
        IF @DeletedRows = 0
            BREAK;
        
        -- Checkpoint after each batch to flush data and logs
        CHECKPOINT;
 
        -- Optional pause to reduce load
        WAITFOR DELAY '00:00:04';
    END;

• Reclaim Unused Data After Large Deletions - Database dependent

            When a large number of records are deleted from a table, it's recommended to rebuild the clustered indexes on the table after cleanup process in MS SQL Server. It helps reclaim unused data pages and improves overall database storage efficiency. We need to apply different approach for other databases such as DB2, Oracle etc., for reclaiming unused space from table. 

ALTER INDEX [indexname] ON [dbo].[tablename] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE)), ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, DATA_COMPRESSION = PAGE)

• Create a procedure with all delete scripts if you plan to run same set of scripts against multiple databases and schedule to run in fixed intervals.

• Identifying Unused Tables and Indexes 

            Identify indexes and tables that aren't being used with help of DBA. Disable the Indexes and drop the tables. Always review the list with customer or business stakeholders before taking any action.

• Cleaning Up Unlinked Attachments

          Extract the attachments from doclinks that aren't linked to any records and delete them from storage. 

select * from doclinks a where a.doclinksid in (select doclinksid from doclinks where (ownertable='WORKORDER' and ownerid not in (select workorderid  from workorder)))

Courtesy: Nausheel Patadia | LinkedIn