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.
-- 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
dbo.tbComputerTarget AS C
LastSyncTime < DATEADD(day, 0 - @computerDeletionTimeThreshold, getutcdate())
-- 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
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.
dbo.tbComputerTarget AS C2
INNER JOIN @tbTmpComputers AS C ON C.TargetID = C2.TargetID
-- Update the Deleted Computers table
SET DeletedTime = getutcdate()
dbo.tbDeletedComputer AS D
INNER JOIN @tbTmpComputers AS C ON C.ComputerID = D.ComputerID
INSERT INTO dbo.tbDeletedComputer (ComputerID)
FROM @tbTmpComputers AS C
NOT EXISTS (SELECT * FROM dbo.tbDeletedComputer WHERE ComputerID = C.ComputerID)