I received the following error when running the ‘Invoke-WsusServerCleanup -CleanupObsoleteComputers’ command on a WSUS instance. From searching through the database the problem appears to have been caused by a computer changing its Target Computer Group, but not being completely unassigned from the old computer group.
Failed to run WSUS Server Cleanup. Error: The DELETE statement conflicted with the REFERENCE constraint "FK__tbExpande__Targe__1881A0DE". The conflict occurred in database "SUSDB", table "dbo.tbExpandedTargetInTargetGroup", column 'TargetID'. The statement has been terminated.
The ‘CleanupObsoleteComputers’ switch invokes a Stored Procedure in the database. This Stored Procedure has been used and modified to clean up the incorrect records, and remove the computers.
1. Connect to the SUSDB instance. For the local Windows Internal Database this is at: \\.\pipe\MICROSOFT##WID\tsql\query
2. Run the following script:
-- Script based on the Stored Procedure spCleanupObsoleteComputers, which is called as part of the cleanup process and was identified to be returning the error above. USE [SUSDB] GO -- Get configuration DECLARE @maximumLastSyncTime DATETIME DECLARE @computerDeletionTimeThreshold INT SELECT @computerDeletionTimeThreshold = CAST(VALUE AS INT) FROM dbo.tbConfiguration WHERE Name = N'ComputerDeletionTimeThreshold' SET @maximumLastSyncTime = DATEADD(day, 0 - @computerDeletionTimeThreshold, getutcdate()) -- Retrieve details of the computers to be removed DECLARE @tbTmpComputers TABLE (TargetID INT PRIMARY KEY, ComputerID NVARCHAR(256), FullDomainName NVARCHAR(MAX)) INSERT INTO @tbTmpComputers (TargetID, ComputerID, FullDomainName) SELECT TOP 500 C.TargetID, C.ComputerID, C.FullDomainName FROM dbo.tbComputerTarget AS C WHERE LastSyncTime < DATEADD(day, 0 - @computerDeletionTimeThreshold, getutcdate()) ORDER BY LastSyncTime ASC -- Return details of the computers that are going to be removed. SELECT * from @tbTmpComputers SELECT * from tbExpandedTargetInTargetGroup tbe INNER JOIN @tbTmpComputers C on tbe.TargetID = C.TargetID -- Remove the invalid links DELETE dbo.tbExpandedTargetInTargetGroup FROM dbo.tbExpandedTargetInTargetGroup AS TBE INNER JOIN @tbTmpComputers AS C ON C.TargetID = TBE.TargetID -- Now process the removal of the computer object from the database. DELETE dbo.tbComputerTarget FROM dbo.tbComputerTarget AS C2 INNER JOIN @tbTmpComputers AS C ON C.TargetID = C2.TargetID -- Update the Deleted Computers table UPDATE dbo.tbDeletedComputer SET DeletedTime = getutcdate() FROM dbo.tbDeletedComputer AS D INNER JOIN @tbTmpComputers AS C ON C.ComputerID = D.ComputerID INSERT INTO dbo.tbDeletedComputer (ComputerID) SELECT ComputerID FROM @tbTmpComputers AS C WHERE NOT EXISTS (SELECT * FROM dbo.tbDeletedComputer WHERE ComputerID = C.ComputerID)