Release: UIM 9.x or later
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:
If, however you receive output that looks like the following, then a support case may need to be opened to fix the issue
Please also note that this query can take 10 - 15 minutes or more to run on a large database.
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(256),@count INT,@count_out INT,@index INT = 1,@tbl_num 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 @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 = 'RN_QOS_DATA_' + RIGHT('0000' + CONVERT(VARCHAR(4), @index), 4);
-- Make sure the table exists before running SQL against it ...
IF EXISTS(SELECT name FROM sysobjects WHERE name = @tbl_char and xtype ='U') BEGIN
SET @craw = ( SELECT isnull((select raw_age from s_qos_definition where r_table = @tbl_char), (SELECT RawAge FROM tbn_de_DataMaintConfig)) + 2 )
SET @sql = N'SELECT @count_out = COUNT(*) FROM [' + @tbl_char + '] WHERE sampletime < (GETDATE() - ' + CAST(@craw AS VARCHAR(5)) + ')';
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 @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@craw AS VARCHAR(4)) + ' days old.';
SET @rows_found = 1;
END
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 = 'HN_QOS_DATA_' + RIGHT('0000' + CONVERT(VARCHAR(4), @index), 4);
-- Make sure the table exists before running SQL against it ...
IF EXISTS(SELECT name FROM sysobjects WHERE name = @tbl_char and xtype = 'U') BEGIN
SET @chist = ( SELECT isnull((select history_age from s_qos_definition where h_table = @tbl_char), (SELECT HistoryAge FROM tbn_de_DataMaintConfig)) + 2 )
SET @sql = N'SELECT @count_out = COUNT(*) FROM [' + @tbl_char + '] WHERE sampletime < (GETDATE() - ' + CAST(@chist AS VARCHAR(5)) + ')';
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 @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@chist AS VARCHAR(4)) + ' days old.';
SET @rows_found = 1;
END
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 = 'DN_QOS_DATA_' + RIGHT('0000' + CONVERT(VARCHAR(4), @index), 4);
-- Make sure the table exists before running SQL against it ...
IF EXISTS(SELECT name FROM sysobjects WHERE name = @tbl_char and xtype = 'U') BEGIN
SET @cday = ( SELECT isnull((select dailyavg_age from s_qos_definition where d_table = @tbl_char), (SELECT DailyAvgAge FROM tbn_de_DataMaintConfig)) + 2 )
SET @sql = N'SELECT @count_out = COUNT(*) FROM [' + @tbl_char + '] WHERE sampletime < (GETDATE() - ' + CAST(@cday AS VARCHAR(5)) + ')';
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 @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@cday AS VARCHAR(4)) + ' days old.';
SET @rows_found = 1;
END
END
SET @index = @index + 2;
END
IF (@rows_found = 0)
PRINT 'No unmaintained data found in DN tables';
--