WSUS: Failed to run WSUS Server Cleanup. Error: The DELETE statement conflicted with the REFERENCE constraint

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)