This issue affects on-premises implementations of FlexNet Manager Suite up to and including 2018 R1.
ImportedFileEvidence
table when a matching record is no longer used in the ImportedInstalledFileEvidence
table may fail, leaving an 'orphaned' record in the ImportedFileEvidence
table (an orphaned record is one that is no longer matched by one in the ImportedInstalledFileEvidence
table). These failures are only visible through database inspection. If significant numbers of these accumulate over time, database performance can be degraded.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:
If you choose to apply the following changes to your ManageSoft reader, you need:
<Reader ?>
tag and ends with a </Reader>
tag.C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Reader\ManageSoft
FileEvidence.xml
and Complete.xml
files in your text editor. In the following edits, be careful not to accidentally delete an adjacent opening or closing tag (<Reader ?>
or </Reader>
) from another element.Complete.xml
file, carefully place your cursor immediately after the last Reader
element, 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 Reader
block.
<!-- 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>
Nov 15, 2018 06:27 PM