iQSonar DB Command History Clean Up

Owned by Conor McCann
Last updated: Jul 25, 2017

The Tables history.t_CommandHistory and history.t_CommandHistoryResult over time can grow very large.

The below SQL Statement will clear down all Command History rows bar those for LMS reporting data.

Step-by-step guide

Open Microsoft SQL Server Management Studio.

  1. Open the iQSonar DB

  2. To view how many rows that are going to be deleted run the following script.


Command History Row Count for Deletion 
INSERT  INTO @StrategyIDs
SELECT  s.StrategyID
FROM    config.t_Strategy s
WHERE   s.Name IN ( 
--    N'SQL Server SESSION', N'SQL Server Users', 
--    N'SQL Server HA Nodes' , N'SQL Server License', 
    N'LMS Execution', N'Oracle DBA Users',
    N'Oracle v$Session', N'Scanning Oracle License',
    N'Oracle v$Instance', N'Oracle 8i v$instance',
    N'Oracle 12c v$instance'
);

-- Count Command rows to be deleted
Select COUNT(1) 
from    history.t_CommandHistory ch
where    1=1
AND        ch.StrategyID NOT IN ( SELECT   StrategyID FROM     @StrategyIDs ) 
-- 129,630
-- 3,371,158

-- Count Result rows to be deleted
Select COUNT(1) 
FROM    history.t_CommandHistory ch
JOIN    history.t_CommandHistoryResult chr ON chr.CommandHistoryID = ch.CommandHistoryID
WHERE    1=1
AND        ch.StrategyID NOT IN ( SELECT   StrategyID FROM     @StrategyIDs )
  1. all Command History rows bar those for LMS reporting data with be deleted.
Command History Clear Down 
-- Delete all Command History rows bar those for LMS reporting

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;

DECLARE @StrategyIDs TABLE ( StrategyID BIGINT );

INSERT  INTO @StrategyIDs
SELECT  s.StrategyID
FROM    config.t_Strategy s
WHERE   s.Name IN ( 
--    N'SQL Server SESSION', N'SQL Server Users', 
--    N'SQL Server HA Nodes' , N'SQL Server License', 
    N'LMS Execution', N'Oracle DBA Users',
    N'Oracle v$Session', N'Scanning Oracle License',
    N'Oracle v$Instance', N'Oracle 8i v$instance',
    N'Oracle 12c v$instance'
);

-- Count Command rows to be deleted
Select COUNT(1)  as CommandCount
from    history.t_CommandHistory ch
where    1=1
AND        ch.StrategyID NOT IN ( SELECT   StrategyID FROM     @StrategyIDs ) 

-- Count Result rows to be deleted
Select        COUNT(1) as ResultCount
FROM        history.t_CommandHistoryResult chr 
LEFT JOIN    history.t_CommandHistory ch ON chr.CommandHistoryID = ch.CommandHistoryID
WHERE        1=1
AND        ch.CommandHistoryID IS NULL


-- Disable FKeys
ALTER TABLE history.t_CommandHistory NOCHECK CONSTRAINT ALL
ALTER TABLE history.t_CommandHistoryResult NOCHECK CONSTRAINT ALL

WHILE (@Deleted_Rows > 0)
  BEGIN
   BEGIN TRANSACTION

    -- Delete a manageable number of rows at a time
    DELETE TOP (4999) ch
    from    history.t_CommandHistory ch
    where    1=1
    AND        ch.StrategyID NOT IN ( SELECT   StrategyID FROM     @StrategyIDs ) 

    SET @Deleted_Rows = @@ROWCOUNT;

   COMMIT TRANSACTION
   CHECKPOINT -- for simple recovery model

END

GO 

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;

WHILE (@Deleted_Rows > 0)
  BEGIN
   BEGIN TRANSACTION

    -- Delete a manageable number of rows at a time
    DELETE TOP (4999) chr
    FROM        history.t_CommandHistoryResult chr 
    LEFT JOIN    history.t_CommandHistory ch ON chr.CommandHistoryID = ch.CommandHistoryID
    WHERE    1=1
    AND        ch.CommandHistoryID IS NULL

    SET @Deleted_Rows = @@ROWCOUNT;

   COMMIT TRANSACTION
   CHECKPOINT -- for simple recovery model

END

-- re-establish FKeys
ALTER TABLE history.t_CommandHistory CHECK CONSTRAINT ALL
ALTER TABLE history.t_CommandHistoryResult CHECK CONSTRAINT ALL

-- Count Command rows remaining
Select COUNT(1) as CommandCount
from    history.t_CommandHistory ch
-- 129,630

-- Count Result rows remaining
Select COUNT(1) as ResultCount
FROM    history.t_CommandHistoryResult chr 
-- 1,562,896