Here is a recipe that may help here.
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.
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.