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

[Elvira_Kan · 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.

[Elvira_Kan · 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 · Accepted solution]

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.

[kevin_christens]

Does deleting from ComplianceHistory also delete from ComplianceHistory_MT and ComplianceHistoryLimited_MT?  We have much older data (3 years older) in ComplianceHistoryLimited_MT than ComplianceHistory_MT so I was wondering if there are database triggers on the ComplianceHistory View to clean both tables or should we run two scripts to clean the tables separately? 

Thank you

[ChrisG]

The short answer is yes: deleting from ComplianceHistory will delete from ComplianceHistory_MT and ComplianceHistoryLimited_MT.

ComplianceHistory itself is a view. In some compliance databases (depending on which versions of FlexNet Manager Suite have been installed in the past), you will find an "INSTEAD OF DELETE" trigger defined on this view which will delete records from the dbo.ComplianceHistory_MT and dbo.ComplianceHistoryLimited_MT tables which underlie the view.

[nrousseau1]

Hello Oliver,

Deleting compliance computer records could take days and eventually cause locks if other activities happen (inventory import).

One approach is to use the script above and schedule a SQL server agent that will run every day from 10:00 AM to 5:00 PM (typically out of the inventory import time). Will select the top 5 million records... deleting by batches of 50K, with a commit every 50K records, to avoid a growth in log files. This is the first attached document.

Another approach that is way more efficient (extremely fast) is attached in the second script: this approach makes sense if the cleanup must be massive and impact potentially several billions of records. The approach is the following:

• Determine which ComplianceHistoryID corresponds to “3 month ago”. This ID will allow filters with way better performance.
• Export all “to be conserved” ComplianceHistory records:
  • Less than 3 month old (old)
  • More than three month old
    • Related to contracts, purchases and Licenses.
  • Truncate the ComplianceHistory Database (which will reset the complianceHstoryIDs)
  • Re-import all conserved records.
  • DROP the “To Be Conserved” History records temp table.

The interest of the scriptare the following

• It "reads" the ComplianceHistory_MT table columns (schema has varied over time) before "constructing" the queries based on the table schema. The script applies to any version of FNMS.
• The proportion of interesting records is very often way smaller than records to delete. Keeping the db and truncating it is more efficient than creating an empty one feeding it with records, dropping the big table and renaming the new table. There a triggers, indexes, constraints (to be renamed) that are making this option complex
• ComplianceHistoryID are reset with a truncate.

and you get for free an FNMS Inventory Computer cleanup script 🙂