History table deletion scripts

[oqueck]

Hi team, customers are asking for some history deletion scripts provided by Flexera to be able to delete all entries in our compliance history table older then 3 years. They want to avoid that this table is getting to big and causes performance and disk space issues. It would be great to get those scripts at least for the main objects like inventories, assets, users and so on and it would be great to get them via our Flexera download center. Is there any change to get this, or maybe some enhancement request about this is already in progress? Thanks & best regards Oliver

[ekan · Accepted solution]

There is a stored procedure ComplianceHistoryPurgeByDate that can be used for that. Please note, deletion might take significant amount of time depending on the size of the database, usage patterns, etc, so special consideration should be taken to choosing time when to run it. Of course, database backup must be taken before running it.

[ChrisG]

Here is a recipe that may help here.

Problem

Over time, the FlexNet Manager Suite compliance database can grow very large. Inspection of the relative sizes of tables in the database will often indicate that the dbo.ComplianceHistory_MT and dbo.ComplianceHistoryLimited_MT tables are the largest tables by an order of magnitude. These tables hold a history of changes made on many different types of records held in the database, and grow in size indefinitely under normal operation of the system.

Solution

A SQL script can be used to delete old and unwanted records from the dbo.ComplianceHistory_MT and dbo.ComplianceHistoryLimited_MT tables (both of which are accessed via the dbo.ComplianceHistory view) in the FlexNet Manager Suite compliance database.

An example of such a script is:

-- Drop existing #ToDelete table if it exists so this script can be re-run easily
IF OBJECT_ID('tempdb..#ToDelete') IS NOT NULL
	DROP TABLE #ToDelete

-- Identify history records to be deleted
SELECT ch.ComplianceHistoryID
INTO #ToDelete
FROM dbo.ComplianceHistory
WHERE <Insert condition here to identify which records you want to delete>

CREATE CLUSTERED INDEX IX_#ToDelete ON #ToDelete(ComplianceHistoryID)

DECLARE @DeletedCount INT
DECLARE @BatchCount INT
SET @BatchCount = 1

WHILE 1=1
BEGIN
	-- Delete in batches of 10,000 records to avoid locking data for too long
	DELETE TOP(10000) ch
	FROM dbo.ComplianceHistory ch
		JOIN #ToDelete d
			ON d.ComplianceHistoryID = ch.ComplianceHistoryID

	SET @DeletedCount = @@ROWCOUNT

	PRINT CONVERT(NVARCHAR, GETDATE())
		+ ': Deletion batch ' + CONVERT(NVARCHAR, @BatchCount)
		+ ': deleted ' + CONVERT(NVARCHAR, @DeletedCount) + ' rows'

	IF @DeletedCount = 0
		BREAK

	SET @BatchCount = @BatchCount + 1

	WAITFOR DELAY '00:00:01' -- Give the database a chance to breathe
END

A script like this is likely to run for many hours or even days if a large amount of data is to be deleted.

If a large number of records get cleaned up in this way at any point, consider shrinking database data file(s) to reclaim disk space.

Configure a script such as this to run on a regular basis (say, weekly over the weekend, or monthly) using a SQL Server Agent job or other scheduling mechanism.

Possible extensions to this script might be:

• Modify the condition used to identify records to be deleted. For example, by filtering on values in various columns in ComplianceHistory it would be possible to delete history records related to application installations being recognized/unrecognized on assets records, while keeping the history of other types of changes.
• The script above makes a halfhearted attempt at running effectively to be able to delete massive numbers (e.g. 100s of millions) of records with minimal interruption to interactive performance. However there are more efficient ways to do this deletion that don't involve so much re-scanning of rows in the #ToDelete temporary table. (If you develop a more efficient deletion script then please post it here.)
• Save a copy of the data that is deleted to a separate “archive” database for future reference if necessary.