Over time, the ComplianceHistory tables in the FlexNet Manager Suite (FNMS) database can grow significantly, potentially leading to performance and storage issues. This article provides guidance on how to safely delete historical records from these tables using SQL scripts and stored procedures.
Affected tables
The following tables typically accumulate the most data:
-
dbo.ComplianceHistory_MT -
dbo.ComplianceHistoryLimited_MT
These tables are accessed via the dbo.ComplianceHistory view, which may include triggers to manage deletions across both tables.
Solution options
Option 1: Use the ComplianceHistoryPurgeByDate stored procedure
Flexera provides a stored procedure called ComplianceHistoryPurgeByDate to delete history data older than a specified date.
NOTE: Execution time varies by database size. Always back up the database before running deletions.
Option 2: Custom batch deletion script
The following SQL script deletes history records in batches to minimize locking and performance impact.
You should update the WHERE condition where specified to identify history records to be deleted. For example, you might do this based on the age of history (based on the HistoryDate), or for history related to particular types of objects.
-- Drop existing temp table if it exists
IF OBJECT_ID('tempdb..#ToDelete') IS NOT NULL
DROP TABLE #ToDelete;
-- Select records for deletion
SELECT ch.ComplianceHistoryID
INTO #ToDelete
FROM dbo.ComplianceHistory
WHERE <Insert condition here, e.g., ch.HistoryDate < DATEADD(YEAR, -3, GETDATE())>;
-- Create index for performance
CREATE CLUSTERED INDEX IX_#ToDelete ON #ToDelete(ComplianceHistoryID);
-- Batch deletion loop
DECLARE @DeletedCount INT;
DECLARE @BatchCount INT = 1;
WHILE 1 = 1
BEGIN
DELETE TOP (10000) ch
FROM dbo.ComplianceHistory ch
JOIN #ToDelete d ON d.ComplianceHistoryID = ch.ComplianceHistoryID;
SET @DeletedCount = @@ROWCOUNT;
PRINT CONVERT(NVARCHAR, GETDATE()) +
': Batch ' + CONVERT(NVARCHAR, @BatchCount) +
' - Deleted ' + CONVERT(NVARCHAR, @DeletedCount) + ' rows';
IF @DeletedCount = 0 BREAK;
SET @BatchCount += 1;
WAITFOR DELAY '00:00:01'; -- Reduce DB load
END
TIP:
• Use SQL Server Agent or another scheduling mechanism to automate this script.
• You can configure a script such as this to run on a regular basis (like weekly over the weekend, or monthly). Run during low-activity periods.
• Shrink the database afterward to reclaim disk space.
Option 3: High-volume truncate and reimport
For databases with billions of rows, a more efficient process may be required:
-
Identify the
ComplianceHistoryIDfor the cutoff date.
-
Export records to keep (e.g., recent changes, licensing info).
-
Truncate the ComplianceHistory table.
-
Re-import only the necessary data.
-
Drop temp/export tables.
This method ensures a clean, fast reset of the table with preserved essential data. It’s especially helpful when the proportion of data to be kept is small.
FAQs
Q: Do I need separate scripts for _MT and Limited_MT tables?
No. The ComplianceHistory view includes a delete trigger that handles both underlying tables.
Q: Can this process lock the database?
Yes. Deleting large volumes of history data may take a long time (many hours, or even many days) and lock tables. Use batching, off-hours scheduling, and commits every 50,000 records to avoid excessive locking and log file growth.
Related Articles
How to enable FlexNet Manager Suite diagnostic tracing 278Number of Views FlexNet Manager Suite log files and locations 191Number of Views Create local file evidence in Flexera One ITAM or FlexNet Manager Suite 53Number of Views VMware stand-alone inventory agent esxquery.exe for FlexNet Manager Suite & Flexera One ITAM 114Number of Views How to configure replication to collect a uploaded agent files on the FlexNet Manager Suite inventory server 43Number 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