Improving database cleanup after importing file evidence in FlexNet Manager Suite
SummaryThis issue affects on-premises implementations of FlexNet Manager Suite up to and including 2018 R1.
This issue affects on-premises implementations of FlexNet Manager Suite up to and including 2018 R1.
Cleanup from the
ImportedFileEvidencetable when a matching record is no longer used in the
ImportedInstalledFileEvidencetable may fail, leaving an 'orphaned' record in the
ImportedFileEvidencetable (an orphaned record is one that is no longer matched by one in the
ImportedInstalledFileEvidencetable). These failures are only visible through database inspection. If significant numbers of these accumulate over time, database performance can be degraded.
CauseThe ManageSoft reader is responsible for this cleanup, but sometimes fails at this task.
The fix described in this knowledge article has been applied to the Flexera cloud instances of FlexNet Manager Suite. It is also included for on-premises customers in the 2018 R2 release of FlexNet Manager Suite. Our strong recommendation is that you upgrade to that release so that the fully-tested fixes are applied for you.
Records in the
ImportedFileEvidence table for which there is no longer a matching record in the
ImportedInstalledFileEvidence table should be deleted. A suitable change is included in the on-premises 2018 R2 release of FlexNet Manager Suite. If you choose to upgrade to that release, you need take no further action. (If you are using a cloud-based instance of FlexNet Manager Suite, the change is already in production.)
The changed code means that the next run of the ManageSoft reader takes longer as it deletes orphaned
ImportedFileEvidence records that should have been previously removed. Thereafter, database performance for file evidence import is improved, as there is a smaller set of data to manipulate.
Choose one of the following workarounds:
- Upgrade to the 2018 R2 release of FlexNet Manager Suite (or a later version).
- Apply the code changes described below.
If you choose to apply the following changes to your ManageSoft reader, you need:
- A flat text editor (or XML editor) of your choice
- Basic understanding of XML
- Basic understanding of your installation of FlexNet Manager Suite
- Basic understanding of how importer procedures work:
- Importer procedures run in blocks loaded from XML files. They run sequentially on the same source type.
- In the reader XML files, each block start with a
<Reader ?>tag and ends with a
- Log in as an administrator to your FlexNet Manager Suite batch server. (In smaller implementations, choose the server that fulfils this function, such as your processing server or your application server.)
- Navigate to the folder containing your Managesoft readers import procedures. By default, they are located in
- Open the
Complete.xmlfiles in your text editor. In the following edits, be careful not to accidentally delete an adjacent opening or closing tag (
</Reader>) from another element.
Tip: Make a backup copy to preserve the original in case you need to recover from your changes.
- In the
Complete.xmlfile, carefully place your cursor immediately after the last
Readerelement, and insert the code block provided under the "ImportedFileEvidence Fix" heading in the "Additional Information" section below. This should now be the last element block in the file. Make sure that the "
Order=" tag has a value higher than the previous
- Carefully check your change(s), and when satisfied, save the modified ManageSoft reader file with its original name in its original location.
- Your changes are exercised at the next full import (normally associated with the license compliance calculations overnight). You may either:
- Wait until tomorrow morning to check your results, or
- Navigate in the web interface to License Compliance > Reconcile, select Update inventory for reconciliation, and click Reconcile. See the online help for more details. (These controls are available only to operators in an Administrator role.)
<!-- FNMS 55210 --> <Reader xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="ExecuteOnTarget" Name="DeleteFromImportedFileEvidence" Order="30" Retries="1"> <![CDATA[ IF EXISTS (SELECT 'X' FROM dbo.ComplianceTenantSetting cts WHERE cts.SettingNameID = 195 AND cts.SettingValue = '1' ) BEGIN -- Creating a temp table for all the ExternalFileId's to be deleted to prevent locking IF OBJECT_ID('tempdb..#ToDeleteExternalFileIDs') IS NOT NULL DROP TABLE #ToDeleteExternalFileIDs CREATE TABLE #ToDeleteExternalFileIDs ( ExternalFileId bigint ) CREATE CLUSTERED INDEX IX_#ToDeleteExternalFileIDs ON #ToDeleteExternalFileIDs(ExternalFileId) INSERT INTO #ToDeleteExternalFileIDs (ExternalFileId) SELECT ife.ExternalFileID FROM dbo.ImportedFileEvidence AS ife WHERE ife.ComplianceConnectionID = @ComplianceConnectionId AND NOT EXISTS ( SELECT 'x' FROM dbo.ImportedInstalledFileEvidence AS iife WHERE iife.ExternalFileID = ife.ExternalFileID AND iife.ComplianceConnectionID = @ComplianceConnectionID ) AND NOT EXISTS ( SELECT 'x' FROM dbo.ImportedInstalledFileEvidenceUsage AS iifeu WHERE iifeu.ExternalFileID = ife.ExternalFileID AND iifeu.ComplianceConnectionID = @ComplianceConnectionID ) PRINT 'Identified ' + CAST(@@ROWCOUNT AS VARCHAR) + ' ImportedFileEvidence records to be deleted' -- End of temp table creation DECLARE @DeletedImportedFileEvidenceCount bigint DECLARE @BatchSize int DECLARE @Rows int DECLARE @MinExternalFileID bigint IF OBJECT_ID('tempdb..#DeletedIDs') IS NOT NULL DROP TABLE #DeletedIDs CREATE TABLE #DeletedIDs ( ExternalFileID bigint ) CREATE CLUSTERED INDEX IX_#DeletedIDs ON #DeletedIDs(ExternalFileId) SELECT TOP 1 @MinExternalFileID = ExternalFileID FROM #ToDeleteExternalFileIDs ORDER BY ExternalFileID SET @Rows = 1 Set @DeletedImportedFileEvidenceCount = 0 SET @BatchSize = ISNULL((SELECT Value FROM dbo.DatabaseConfiguration WHERE Property = 'FileEvidenceReaderBatchSize'), 1000000); WHILE @Rows > 0 BEGIN WITH ToBeDeleted AS ( SELECT TOP(@BatchSize) ife.* FROM dbo.ImportedFileEvidence AS ife WHERE ife.ExternalFileID >= @MinExternalFileID AND ife.ComplianceConnectionID = @ComplianceConnectionId AND ife.ExternalFileID IN ( SELECT td.ExternalFileId FROM #ToDeleteExternalFileIDs AS td WHERE td.ExternalFileID >= @MinExternalFileID ) ) DELETE FROM ToBeDeleted OUTPUT deleted.ExternalFileID INTO #DeletedIDs SET @Rows = @@ROWCOUNT SET @DeletedImportedFileEvidenceCount = @DeletedImportedFileEvidenceCount + @Rows SELECT TOP 1 @MinExternalFileID = ExternalFileID FROM #DeletedIDs ORDER BY ExternalFileID DESC TRUNCATE TABLE #DeletedIDs END DROP TABLE #DeletedIDs DROP TABLE #ToDeleteExternalFileIDs PRINT N'Removed ' + CONVERT(NVARCHAR(1000), @DeletedImportedFileEvidenceCount) + N' ImportedFileEvidence records in ImportedFileEvidence staging table' END ]]> </Reader>