This is a forum solution that has been preserved as an article for formatting. The original forum thread can be found here: https://community.flexera.com/s/question/0D5PL00000NwojL0AR/history-table-deletion-scripts

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.

Additional information

Also see the following article for an alternate discussion about this topic: Inventory Computer and History deletion scripts.