Data_engine probe - The maintenance job not getting triggered automatically from data_engine probe - still have old data in the DB

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

The maintenance job not getting triggered automatically from data_engine probe for some reason due DB with no best practice / Environment problems and have no DBA to help

Environment

Release : 20.3.3 or later

Component : UIM - DATA_ENGINE

Resolution

Manually you can execute the stored procedure from scheduler. ( WE RECOMMEND YOU TO DO FIRST IN TEST ENVIROMENT OR DO A DB BACKUP FIRST)

  

1 - Please disable data maintenance before executing below stored procedure. deactivate and activate data engine probe.
 

2 - Execute below stored procedure from agent scheduler for one time.
 

DECLARE    @return_value int,
    @pQosDefId int,
    @current_time datetime,
    @pErrorMessage ndtLongString

DECLARE qosid CURSOR FOR
     select  qos_def_id from S_QOS_DEFINITION order by qos_def_id asc

OPEN qosid;
    FETCH NEXT FROM qosid INTO @pQosDefId

WHILE (@@FETCH_STATUS = 0)

    BEGIN
        SELECT  @current_time = GETDATE();
        EXEC    @return_value = [dbo].[spn_de_DataMaint]
            @pQosDefId = @pQosDefId,
            @pMode = 'all',
            @pCurrentTime = @current_time

        RAISERROR('qosid %d done. rc = %d. error = %s.',0,1,
            @pQosDefId,
            @return_value,
            @pErrorMessage) WITH NOWAIT

        FETCH NEXT FROM qosid INTO @pQosDefId

    END

CLOSE qosid;
DEALLOCATE qosid;

Below are the steps in detail about partition range of tables and its purge process:
 

1. will query the current retention days of table based on type of table i.e.RN or HN or DN or BN from tbn_de_datamaintconfig table.
2. will check the start boundary value range and end boundary value range based on the retention of RN or HN or DN or BN tables
3. will query the boundary value from partition_range_values system table to determine the purge of the old partitions of the table.
4. after capturing the above information,if there are no partitions and the sql server version is enterprise it will create the partitions 
5. followed by it will call spn_de_PartitionAdmin__DropData stored procedure to truncate old partitions and then merge truncated partition(dropping partition).
6. It will create the future partitions.