data_engine maintenance job deleting data daily for all devices

book

Article ID: 145508

calendar_today

Updated On:

Products

NIMSOFT PROBES DX Infrastructure Management

Issue/Introduction

Noticed that UIM data_engine is deleting data daily. Whenever we produce graph for any device it shows data after 00:30 am, Last thursday we removed the daily schedule that was normalizing data at 00:40 am and set it to yearly and Sunday it showed data from Thursday, then I enabled it Monday again to daily and again it started showing data from 00:40.

On recommendation yesterday I again set it to yearly but still I can see data after 00:40 am.

Cause

- the cause of this case is an improper partitioning in the database server

Environment

Release : 9.2.0

Resolution

Run below stored procedures manually in the same sequence and order which will create partitions and once these all below steps done, please execute the last stored procedure which will perform manual data maintenance. 

Please disable data maintenance job, before starting these scripts.

To validate whether data maintenance job is disabled or not, check in data_engine probe folder data_engine.cfg, if the value set to data_management_active = no which means data maintenance job disabled. 

Also make sure there are no index maintenance jobs running during this activity.

Please capture the time it took to complete each and every stored procedure. Please share the log output also.(these stored procedures may take time to complete, therefore please wait  for successful completion of execution).

First run below stored procedure and followed by  run sequentially one by one

1.

DECLARE        @return_value int,
        @pErrorMessage ndtLongString,
        @table_name varchar(17),
        @table_type varchar(2),
        @current_time datetime;

SET @table_type = 'RN';
DECLARE RN_tblname CURSOR FOR
        SELECT name from sys.objects WHERE name like 'RN_QOS_DATA%' order by name asc

OPEN RN_tblname;
     FETCH NEXT FROM RN_tblname INTO @table_name

WHILE (@@FETCH_STATUS = 0)

    BEGIN
          /* Un-partioning */

               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__UnpartitionTable]
                    @pTableName = @table_name,
                    @pTableType = @table_type,
                    @pLogLevel = 5

        RAISERROR('TableName %s Un-partioning done. rc = %d. error = %s.',0,1,
            @table_name,
            @return_value,
            @pErrorMessage) WITH NOWAIT

        /* Partioning */

               SELECT    @current_time = GETDATE();
               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__PartitionTable] 
                @pTableName = @table_name, 
                    @pTableType = @table_type,
                    @pCurrentTime = @current_time, 
                    @pLogLevel = 5

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

        FETCH NEXT FROM RN_tblname INTO @table_name
    END

CLOSE RN_tblname;
DEALLOCATE RN_tblname;


2. after completion of above SP run below stored procedure:


DECLARE        @return_value int,
        @pErrorMessage ndtLongString,
        @table_name varchar(17),
        @table_type varchar(2),
        @current_time datetime;

SET @table_type = 'HN';

DECLARE HN_tblname CURSOR FOR
        SELECT name from sys.objects WHERE name like 'HN_QOS_DATA%' order by name asc

OPEN HN_tblname;
     FETCH NEXT FROM HN_tblname INTO @table_name

WHILE (@@FETCH_STATUS = 0)

    BEGIN
          /* Un-partioning */

               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__UnpartitionTable]
                    @pTableName = @table_name,
                    @pTableType = @table_type,
                    @pLogLevel = 5

        RAISERROR('TableName %s Un-partioning done. rc = %d. error = %s.',0,1,
            @table_name,
            @return_value,
            @pErrorMessage) WITH NOWAIT

        /* Partioning */

               SELECT    @current_time = GETDATE();
               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__PartitionTable] 
                @pTableName = @table_name, 
                    @pTableType = @table_type,
                    @pCurrentTime = @current_time, 
                    @pLogLevel = 5

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

        FETCH NEXT FROM HN_tblname INTO @table_name
    END

CLOSE HN_tblname;
DEALLOCATE HN_tblname;


3. upon completion of above stored procedure,run below stored procedure:


DECLARE        @return_value int,
        @pErrorMessage ndtLongString,
        @table_name varchar(17),
        @table_type varchar(2),
        @current_time datetime;

SET @table_type = 'DN';

DECLARE DN_tblname CURSOR FOR
        SELECT name from sys.objects WHERE name like 'DN_QOS_DATA%' order by name asc

OPEN DN_tblname;
     FETCH NEXT FROM DN_tblname INTO @table_name

WHILE (@@FETCH_STATUS = 0)

    BEGIN
          /* Un-partioning */

               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__UnpartitionTable]
                    @pTableName = @table_name,
                    @pTableType = @table_type,
                    @pLogLevel = 5

        RAISERROR('TableName %s Un-partioning done. rc = %d. error = %s.',0,1,
            @table_name,
            @return_value,
            @pErrorMessage) WITH NOWAIT

        /* Partioning */

               SELECT    @current_time = GETDATE();
               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__PartitionTable] 
                @pTableName = @table_name, 
                    @pTableType = @table_type,
                    @pCurrentTime = @current_time, 
                    @pLogLevel = 5

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

        FETCH NEXT FROM DN_tblname INTO @table_name
    END

CLOSE DN_tblname;
DEALLOCATE DN_tblname;

4. upon completion of above script, please execute below stored procedure.


DECLARE        @return_value int,
        @pErrorMessage ndtLongString,
        @table_name varchar(17),
        @table_type varchar(2),
        @current_time datetime;

SET @table_type = 'BN';

DECLARE BN_tblname CURSOR FOR
        SELECT name from sys.objects WHERE name like 'BN_QOS_DATA%' order by name asc

OPEN BN_tblname;
     FETCH NEXT FROM BN_tblname INTO @table_name

WHILE (@@FETCH_STATUS = 0)

    BEGIN
          /* Un-partioning */

               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__UnpartitionTable]
                    @pTableName = @table_name,
                    @pTableType = @table_type,
                    @pLogLevel = 5

        RAISERROR('TableName %s Un-partioning done. rc = %d. error = %s.',0,1,
            @table_name,
            @return_value,
            @pErrorMessage) WITH NOWAIT

        /* Partioning */

               SELECT    @current_time = GETDATE();
               EXEC    @return_value = [dbo].[spn_de_PartitionAdmin__PartitionTable] 
                @pTableName = @table_name, 
                    @pTableType = @table_type,
                    @pCurrentTime = @current_time, 
                    @pLogLevel = 5

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

        FETCH NEXT FROM BN_tblname INTO @table_name
    END

CLOSE BN_tblname;
DEALLOCATE BN_tblname;

5.
Execute below stored procedure which perform Data maintenance.


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

DECLARE qosid CURSOR FOR
    SELECT DISTINCT qos_def_id FROM S_QOS_DATA 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;

Attachments

1582210447485__RN_HN_DN_BN_partition_SPs.txt get_app