Verifying data_engine is deleting old raw and historic data properly
search cancel

Verifying data_engine is deleting old raw and historic data properly

book

Article ID: 34940

calendar_today

Updated On:

Products

DX Unified Infrastructure Management (Nimsoft / UIM) CA Unified Infrastructure Management On-Premise (Nimsoft / UIM) CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

How can we verify that the data_engine is deleting old raw and historic data properly from the database?

Environment

Release:  UIM 9.x or later

Resolution

  1. Access your SQL server's desktop
    Log into a SQL Management Studio console
    Select the appropriate database from the dropdown in the toolbar.  This will usually be NimsoftSLM for older installations or CA_UIM for newer.
    Paste the following query into a New Query window and click the Execute button

    /****** Script for finding old data which the data_engine failed to summarize ******/ 

    DECLARE @sql NVARCHAR(256),@tbl_char VARCHAR(4),@count INT,@count_out INT,@index INT = 1,@tbl_num INT,@raw_days INT,@hist_days INT,@daily_days INT,@rows_found TINYINT = 0,@rows_out NVARCHAR(128),@params NVARCHAR(256) = N'@rowcountOUT INT, @count_out INT OUTPUT'; 

    SET @raw_days = (SELECT RawAge FROM tbn_de_DataMaintConfig) + 2; -- Days where the data_engine should delete raw data 

    SET @hist_days = (SELECT HistoryAge FROM tbn_de_DataMaintConfig) + 2; -- Days where the data_engine should delete hourly data 

    SET @daily_days = (SELECT DailyAvgAge FROM tbn_de_DataMaintConfig) + 2; -- Days where the data_engine should delete daily data
     

    -- Set @tbl_num to be the number of raw tables (a count of the S_QOS_DEFINTION table) 

    SET @sql = N'SELECT @count_out = COUNT(*) FROM S_QOS_DEFINITION'; 

    EXEC sys.sp_executesql @sql, @params, @rowcountOUT = @count, @count_out = @rows_out OUTPUT; 

    SET @tbl_num = CAST(@rows_out AS INT);
     

    -- Search the RN tables for data that should have been removed 

    WHILE (@index <= @tbl_num) BEGIN 

    SET @tbl_char = RIGHT('0000' + CONVERT(VARCHAR(4), @index), 4); 

    SET @sql = N'SELECT @count_out = COUNT(*) FROM RN_QOS_DATA_' + @tbl_char + ' WHERE sampletime < (GETDATE() - ' + CAST(@raw_days AS VARCHAR(5)) + ')';
     

    -- Make sure the table exists before running SQL against it ...

     IF EXISTS(SELECT name FROM sysobjects WHERE name = N'RN_QOS_DATA_'+ @tbl_char and xtype ='U') 

    EXEC sys.sp_executesql @sql, @params, @rowcountOUT = @count, @count_out = @rows_out OUTPUT;
     

    -- Print the output of old raw data

     IF (CAST(@rows_out AS INT) > 0) BEGIN 

    PRINT 'RN_QOS_DATA_' + @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@raw_days AS VARCHAR(4)) + ' days old.'; 

    SET @rows_found = 1; 

    END 

    SET @index = @index + 2; 

    END 

    IF (@rows_found = 0)

    PRINT 'No unmaintained data found in RN tables';

    -- Reset our index variable to traverse HN tables 

    SET @index = 1; 

    SET @rows_found = 0;
     

    -- Search the HN tables for data that should have been removed 

    WHILE (@index <= @tbl_num) BEGIN 

    SET @tbl_char = RIGHT('0000' + CONVERT(VARCHAR(4), @index), 4); 

    SET @sql = N'SELECT @count_out = COUNT(*) FROM [HN_QOS_DATA_' + @tbl_char + '] WHERE sampletime < (GETDATE() - ' + CAST(@hist_days AS VARCHAR(5)) + ')';
     

    -- Make sure the table exists before running SQL against it ... 

    IF EXISTS(SELECT name FROM sysobjects WHERE name = N'HN_QOS_DATA_' + @tbl_char and xtype = 'U') 

    EXEC sys.sp_executesql @sql, @params, @rowcountOUT = @count, @count_out = @rows_out OUTPUT; 


    -- Print the output of old historic data 

    IF (CAST(@rows_out AS INT) > 0) BEGIN 

    PRINT 'HN_QOS_DATA_' + @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@hist_days AS VARCHAR(4)) + ' days old.'; 

    SET @rows_found = 1; 

    END 

    SET @index = @index + 2; 

    END 

    IF (@rows_found = 0) 

    PRINT 'No unmaintained data found in HN tables'; 

    -- Reset our index variable to traverse DN tables 

    SET @index = 1; 

    SET @rows_found = 0;
     

    -- Search the DN tables for data that should have been removed 

    WHILE (@index <= @tbl_num) BEGIN 

    SET @tbl_char = RIGHT('0000' + CONVERT(VARCHAR(4), @index), 4); 

    SET @sql = N'SELECT @count_out = COUNT(*) FROM [DN_QOS_DATA_' + @tbl_char + '] WHERE sampletime < (GETDATE() - ' + CAST(@daily_days AS VARCHAR(5)) + ')';
     

    -- Make sure the table exists before running SQL against it ... 

    IF EXISTS(SELECT name FROM sysobjects WHERE name = N'DN_QOS_DATA_' + @tbl_char and xtype = 'U') 

    EXEC sys.sp_executesql @sql, @params, @rowcountOUT = @count, @count_out = @rows_out OUTPUT;
     

    -- Print the output of old historic data 

    IF (CAST(@rows_out AS INT) > 0) BEGIN 

    PRINT 'DN_QOS_DATA_' + @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@hist_days AS VARCHAR(4)) + ' days old.'; 

    SET @rows_found = 1; 

    END 

    SET @index = @index + 2;

    END 

    IF (@rows_found = 0) 

    PRINT 'No unmaintained data found in DN tables'; 

    /****** End SQL Script ******/
     

    If the output from the script shows as follows then data_engine is honoring the retention policies configured in data_engine:



No unmaintained data found in RN tables
No unmaintained data found in HN tables
No unmaintained data found in DN tables

If, however you receive output that looks like the following, then a support case may need to be opened to fix the issue

        RN_QOS_DATA_0001 contains 416 samples which are older than 30 days old.
        RN_QOS_DATA_0007 contains 2524 samples which are older than 30 days old.
        RN_QOS_DATA_0008 contains 415 samples which are older than 30 days old.
 

Please also note that this query can take 10 - 15 minutes or more to run on a large database.

Additional Information

If you have configured some QoS in your environment to have a different (non-default) retention period the above script will not return accurate results.

If that is the case please use the below version:

--

DECLARE @sql NVARCHAR(256),@tbl_char VARCHAR(4),@count INT,@count_out INT,@index INT = 1,@tbl_num INT,@raw_days INT,@hist_days INT,@daily_days INT,@craw int, @chist int, @cday int,@rows_found TINYINT = 0,@rows_out NVARCHAR(128),@params NVARCHAR(256) = N'@rowcountOUT INT, @count_out INT OUTPUT';

 

SET @raw_days = (SELECT RawAge FROM tbn_de_DataMaintConfig) + 2; -- Days where the data_engine should delete raw data

SET @hist_days = (SELECT HistoryAge FROM tbn_de_DataMaintConfig) + 2; -- Days where the data_engine should delete hourly data

SET @daily_days = (SELECT DailyAvgAge FROM tbn_de_DataMaintConfig) + 2; -- Days where the data_engine should delete daily data

 

 

-- Set @tbl_num to be the number of raw tables (a count of the S_QOS_DEFINTION table)

SET @sql = N'SELECT @count_out = COUNT(*) FROM S_QOS_DEFINITION';

EXEC sys.sp_executesql @sql, @params, @rowcountOUT = @count, @count_out = @rows_out OUTPUT;

SET @tbl_num = CAST(@rows_out AS INT);

 

-- Search the RN tables for data that should have been removed

WHILE (@index <= @tbl_num) BEGIN

SET @tbl_char = RIGHT('0000' + CONVERT(VARCHAR(4), @index), 4);

SET @craw = (SELECT raw_age from s_qos_definition where r_table = 'RN_QOS_DATA_' + @tbl_char)

if (@craw is null)
 BEGIN
  SET @sql = N'SELECT @count_out = COUNT(*) FROM RN_QOS_DATA_' + @tbl_char + ' WHERE sampletime < (GETDATE() - ' + CAST(@raw_days AS VARCHAR(5)) + ')';
 END
ELSE
 BEGIN
SET @sql = N'SELECT @count_out = COUNT(*) FROM RN_QOS_DATA_' + @tbl_char + ' WHERE sampletime < (GETDATE() - ' + CAST(@craw AS VARCHAR(5)) + ')';
 END
 

-- Make sure the table exists before running SQL against it ...

IF EXISTS(SELECT name FROM sysobjects WHERE name = N'RN_QOS_DATA_'+ @tbl_char and xtype ='U')

EXEC sys.sp_executesql @sql, @params, @rowcountOUT = @count, @count_out = @rows_out OUTPUT;

 

-- Print the output of old raw data 

IF (CAST(@rows_out AS INT) > 0) BEGIN

IF (@craw is null)
 BEGIN
PRINT 'RN_QOS_DATA_' + @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@raw_days AS VARCHAR(4)) + ' days old.';
 END
ELSE
 BEGIN
 PRINT 'RN_QOS_DATA_' + @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@craw AS VARCHAR(4)) + ' days old.';
 END
SET @rows_found = 1;

END

 

SET @index = @index + 2;

END

 

IF (@rows_found = 0)

PRINT 'No unmaintained data found in RN tables';

 

-- Reset our index variable to traverse HN tables

SET @index = 1;

SET @rows_found = 0;

 

-- Search the HN tables for data that should have been removed

WHILE (@index <= @tbl_num) BEGIN

SET @tbl_char = RIGHT('0000' + CONVERT(VARCHAR(4), @index), 4);
SET @chist = (SELECT history_age from s_qos_definition where r_table = 'RN_QOS_DATA_' + @tbl_char)

IF (@chist is null)
 BEGIN
SET @sql = N'SELECT @count_out = COUNT(*) FROM [HN_QOS_DATA_' + @tbl_char + '] WHERE sampletime < (GETDATE() - ' + CAST(@hist_days AS VARCHAR(5)) + ')';
 END
ELSE
 BEGIN
SET @sql = N'SELECT @count_out = COUNT(*) FROM [HN_QOS_DATA_' + @tbl_char + '] WHERE sampletime < (GETDATE() - ' + CAST(@chist AS VARCHAR(5)) + ')';
 END
 

-- Make sure the table exists before running SQL against it ...

IF EXISTS(SELECT name FROM sysobjects WHERE name = N'HN_QOS_DATA_' + @tbl_char and xtype = 'U')

EXEC sys.sp_executesql @sql, @params, @rowcountOUT = @count, @count_out = @rows_out OUTPUT;

 

-- Print the output of old historic data

IF (CAST(@rows_out AS INT) > 0) BEGIN

IF (@chist is null)
 BEGIN
PRINT 'HN_QOS_DATA_' + @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@hist_days AS VARCHAR(4)) + ' days old.';
 END
ELSE
 BEGIN
 PRINT 'HN_QOS_DATA_' + @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@chist AS VARCHAR(4)) + ' days old.';
 END
SET @rows_found = 1;

END

 

SET @index = @index + 2;

END

 

IF (@rows_found = 0)

PRINT 'No unmaintained data found in HN tables';

 

-- Reset our index variable to traverse DN tables

SET @index = 1;

SET @rows_found = 0;

 

-- Search the DN tables for data that should have been removed

WHILE (@index <= @tbl_num) BEGIN

SET @tbl_char = RIGHT('0000' + CONVERT(VARCHAR(4), @index), 4);
SET @cday = (SELECT dailyavg_age from s_qos_definition where r_table = 'RN_QOS_DATA_' + @tbl_char)

IF (@cday is null)
 BEGIN
SET @sql = N'SELECT @count_out = COUNT(*) FROM [DN_QOS_DATA_' + @tbl_char + '] WHERE sampletime < (GETDATE() - ' + CAST(@daily_days AS VARCHAR(5)) + ')';
 END
ELSE
 BEGIN
 SET @sql = N'SELECT @count_out = COUNT(*) FROM [DN_QOS_DATA_' + @tbl_char + '] WHERE sampletime < (GETDATE() - ' + CAST(@cday AS VARCHAR(5)) + ')';
 END
 

-- Make sure the table exists before running SQL against it ...

IF EXISTS(SELECT name FROM sysobjects WHERE name = N'DN_QOS_DATA_' + @tbl_char and xtype = 'U')

EXEC sys.sp_executesql @sql, @params, @rowcountOUT = @count, @count_out = @rows_out OUTPUT;

 

-- Print the output of old historic data

IF (CAST(@rows_out AS INT) > 0) BEGIN

IF (@cday is null)
 BEGIN
PRINT 'DN_QOS_DATA_' + @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@hist_days AS VARCHAR(4)) + ' days old.';
 END
ELSE
 BEGIN
 PRINT 'DN_QOS_DATA_' + @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@cday AS VARCHAR(4)) + ' days old.';
 END
SET @rows_found = 1;

END

 

SET @index = @index + 2;

END

 

IF (@rows_found = 0)

PRINT 'No unmaintained data found in DN tables';

--

 

Attachments

1693318242713__find_old_data.sql get_app
Powered by Wolken Software