--
WARNING!!!
-- Consult with your DBA before using this script and do test it first in a UIM Test/DEV/Staging environment.-- data deletion script with use of date and top argument
-- Note that the TOP (<value>) may be increased until all of the unwanted data has been purged from the raw data (RN) tables.
-- Note that the delete statement uses the sampletime value which can be modified to allow deletion of more (or less) data.
-- Ensure that the data_management_timeout setting in the data_engine is set to 65536 not 7200 before you run this query
-- Ask your DBA to make sure there is no memory pressure on the backendĀ database server and if there is, add-dedicate more memory to the DB server
-- This query references S_QOS_DATA and deletes any raw data from RN tables where the table_id is NOT in the S_QOS_DATA table
----- raw data purge script -----
DECLARE qos_def_cursor CURSOR READ_ONLY FAST_FORWARD FOR SELECT DISTINCT qos_def_id FROM S_QOS_DATA;
DECLARE @sql NVARCHAR(1000);
DECLARE @r_table VARCHAR(64);
DECLARE @h_table VARCHAR(64);
DECLARE @qos_def_id VARCHAR(4);
DECLARE @increment int;
DECLARE @loop_increment int;
DECLARE @rows int;
-- SET INITIAL INCREMENT
-- IT WILL DOUBLE EACH RUN UNTIL THE DATA IS DELETED
SET @increment = 1000;
SET @loop_increment = @increment;
OPEN qos_def_cursor
FETCH NEXT FROM qos_def_cursor INTO @qos_def_id WHILE @@FETCH_STATUS = 0
BEGIN
SET @r_table = 'RN_QOS_DATA_' + RIGHT('0000' + @qos_def_id, 4);
SET @h_table = 'HN_QOS_DATA_' + RIGHT('0000' + @qos_def_id, 4);
SET @sql = 'DELETE TOP (3000) FROM ' + @r_table + ' WHERE sampletime < ''2017-06-23 00:00:00.000''';
SET @sql = 'SELECT @cnt=COUNT(*) FROM ' + @r_table + ' WHERE table_id NOT IN (SELECT table_id FROM S_QOS_DATA WHERE r_table = ''' + @r_table + ''')';
EXECUTE sp_executesql @sql, N'@r_table varchar(64),@cnt int OUTPUT', @
[email protected]_table, @
[email protected] OUTPUT
PRINT CAST(@rows as VARCHAR);
loop:
IF @increment < @rows
-- Start LOOP
BEGIN
PRINT CHAR(13) + CHAR(13) + 'Deleting top ' + CAST(@increment AS VARCHAR) + ' from ' + @r_table + ' Total: ' + CAST(@rows AS VARCHAR);
SET @sql = 'DELETE TOP (' + CAST(@loop_increment AS VARCHAR) + ') FROM ' + @r_table + ' WHERE table_id NOT IN (SELECT table_id FROM S_QOS_DATA where r_table = ''' + @r_table + ''')';
EXECUTE sp_executesql @sql, N'@r_table varchar(64), @loop_increment int', @r_table = @r_table, @loop_increment = @loop_increment;
SET @rows = @rows - @loop_increment;
SET @loop_increment = @loop_increment * 2;
GOTO loop;
END
ELSE
BEGIN
PRINT CHAR(13) + CHAR(13) + 'Deleting top ' + CAST(@increment AS VARCHAR) + ' from ' + @r_table;
END
SET @loop_increment = @increment;
FETCH NEXT FROM qos_def_cursor INTO @qos_def_id;
END
CLOSE qos_def_cursor;
DEALLOCATE qos_def_cursor;