-- Delete obsolete records that do not exist anymore DECLARE @DeletedInstalledFileEvidenceCount BIGINT DECLARE @BatchSize INT DECLARE @Rows INT SET @Rows = 1 SET @DeletedInstalledFileEvidenceCount = 0 WHILE @Rows > 0 BEGIN SET @BatchSize = ISNULL((SELECT Value FROM dbo.DatabaseConfiguration WHERE Property = 'FileEvidenceWriterBatchSize'), 1000000) DELETE TOP(@BatchSize) dbo.InstalledFileEvidence WHERE NOT EXISTS ( SELECT 'x' FROM #TmpInstalledFileEvidence AS tife WHERE tife.RowNumber = 1 AND tife.FileEvidenceID = InstalledFileEvidence.FileEvidenceID AND tife.ComplianceComputerID = InstalledFileEvidence.ComplianceComputerID AND tife.AccessModeID = InstalledFileEvidence.AccessModeID) SET @Rows = @@ROWCOUNT SET @DeletedInstalledFileEvidenceCount = @DeletedInstalledFileEvidenceCount + [~comments] -- BEGIN CUSTOMISATION -- We are done as soon as fewer than @BatchSize rows got deleted IF @Rows < @BatchSize BREAK -- END CUSTOMISATION END PRINT N'Removed ' + CAST(@DeletedInstalledFileEvidenceCount AS nvarchar(MAX)) + N' records from InstalledFileEvidence that no longer exist in data sources'
Jan 30, 2019 02:40 AM