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.
Related Articles
Inventory Computer and History deletion scripts 55Number of Views Snow Inventory Agent package post install script failure on Ubuntu 79Number of Views ServiceNow export omits deletion signal for computers archived by bulk deletion, causing them to be recreated by ITV/TDS r… 3Number of Views Data in transform histories ignored when updating native tables after pulling data from IT Visibility to ServiceNow 23Number of Views FlexNet Code Insight Workspace Export Import Scripts - Add-On 3Number of Views
Hi, I am Reva - Ask me anything.
No new updates
Thanks for the feedback!
Your feedback has been saved.Rate this response:
Add Additional feedback ( Optional )
Are you sure you want to cancel
the case creation?
Are you sure you want to cancel the case creation?
Are you sure you want to close this case
| Products | Region | Phone Numbers |
|---|---|---|
| FlexNet Operations FlexNet Embedded FlexNet Publisher FlexNet Connect FlexNet Code Insight InstallAnywhere InstallShield |
North America * |
+1 630-332-2513 (toll) +1 877-279-2853 (toll-free in North America) |
| Europe * |
+44 1925 944367 (toll) +44 800 047 8642 (toll-free in Europe) |
|
| Japan * | +81 3-4540-5335 (select option 2) | |
| Australia * |
+61 3 9895 2177 +61 1800 560 603 (toll-free in Australia) |
|
|
Usage Intelligence (formerly
Revulytics) Compliance Intelligence |
Please use the Case Portal to submit your support ticket or reach out to your Revenera contact. | |
Case id: 00001065
Activity: Status change: 2 hours ago