How to delete data manually from the backend database tables in UIM
Article ID: 109742
Updated On:
Products
DX Infrastructure Management
NIMSOFT PROBES
Issue/Introduction
A customer was experiencing intermittent slow performance issues in USM and CABI displays due to too much data from large tables and data management not completing (data management was accidentally disabled for a long time).
Environment
- UIM 8.5.1
- Microsoft SQL Server
- Microsoft SQL Server
Resolution
-- 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;
-- 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;
Feedback
Yes
No
Powered by
