Repeated error on WSUS Server Cleanup Wizard

Horatio Rooks 6 Reputation points
2021-12-18T23:11:34.227+00:00

Repeated error on WSUS Server Cleanup Wizard - Unused updates and update revisions

-------------------------------------------------------------------------------------------------

The WSUS administration console was unable to connect to the WSUS Server Database.

Verify that SQL server is running on the WSUS Server. If the problem persists, try restarting SQL.

System.Data.SqlClient.SqlException -- Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Source
.Net SqlClient Data Provider

Stack Trace:
at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
at Microsoft.UpdateServices.UI.SnapIn.Wizards.ServerCleanup.ServerCleanupWizard.OnCleanupComplete(Object sender, PerformCleanupCompletedEventArgs e)

-----------------------------------------------------

I can and have repeatedly run all of the individual Cleanup options, but the cleanup wizard fails repeatedly when attempting to run the option 'Unused updates and update revisions'.

The server has also been restarted, this has not helped.
The MSSQLSERVER service has been restarted without this being of any help.

If anyone reading this has a solution of useful remediation steps please share.

Many Thanks
158776-msprdsccm01-server-cleanup-database-error-clean.png

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,988 questions
Windows Server
Windows Server
A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.
13,661 questions
0 comments No comments
{count} vote

5 answers

Sort by: Most helpful
  1. Adam J. Marshall 9,686 Reputation points MVP
    2021-12-18T23:28:49.213+00:00

    Have you done any of the other parts of the monthly wsus maintenance including the SQL reindexing script, declining superseded updates, and more?

    https://www.ajtek.ca/wsus/how-to-setup-manage-and-maintain-wsus-part-8-wsus-server-maintenance/

    1 person found this answer helpful.

  2. Adam J. Marshall 9,686 Reputation points MVP
    2021-12-19T00:20:19.967+00:00

    Yes, but the other items related to the WSUS maintenance are almost more important than the server cleanup wizard (SCW) alone.

    1 person found this answer helpful.
    0 comments No comments

  3. Frank Cedeno 6 Reputation points
    2022-07-13T15:36:09.82+00:00

    If you are confident in tsql you can update the sql script for spGetObsoleteUpdatesToCleanup and comment out the Select Statement (the whole select statement but only the select statement. That is starting in "Select Distinct" and ending in Order By u.LocalUpdateID DESC") immediately under what you commented out and before the "RETURN(0)" statement, paste the code below:

    CREATE TABLE #Bundles (localID INT PRIMARY KEY)
    INSERT #Bundles SELECT r1.LocalUpdateID FROM dbo.tbBundleDependency bd
    INNER JOIN dbo.tbRevision r1 ON bd.BundledRevisionID = r1.RevisionID GROUP BY r1.LocalUpdateID

    CREATE TABLE #PreReq (LocalID INT PRIMARY KEY)
    INSERT #PreReq SELECT r2.LocalUpdateID FROM dbo.tbPrerequisiteDependency pd
    INNER JOIN dbo.tbRevision r2 ON pd.PrerequisiteRevisionID = r2.RevisionID GROUP BY r2.LocalUpdateID

    CREATE TABLE #Dep (LocalID INT PRIMARY KEY)
    INSERT #Dep SELECT r3.LocalUpdateID FROM dbo.tbDeployment d
    INNER JOIN dbo.tbRevision r3 ON d.RevisionID = r3.RevisionID
    WHERE d.TargetGroupTypeID = 0
    AND d.ActionID IN (0, 1, 3) GROUP BY r3.LocalUpdateID
    CREATE TABLE #Dead (LocalID INT PRIMARY KEY)
    INSERT #Dead SELECT r4.LocalUpdateID FROM dbo.tbDeadDeployment dd
    INNER JOIN dbo.tbRevision r4 ON dd.RevisionID = r4.RevisionID
    WHERE dd.TargetGroupTypeID = 0
    AND dd.ActionID IN (0, 1, 3)
    AND dd.TimeOfDeath > @minimumDeadDeploymentTime
    GROUP BY r4.LocalUpdateID

    SELECT DISTINCT u.LocalUpdateID FROM dbo.tbUpdate u
    INNER JOIN dbo.tbRevision r ON r.LocalUpdateID = u.LocalUpdateID
    INNER JOIN dbo.tbProperty p ON p.RevisionID = r.RevisionID
    LEFT OUTER JOIN #Bundles b ON b.localID = u.LocalUpdateID
    LEFT OUTER JOIN #PreReq pr ON u.localUpdateID = pr.localID
    LEFT OUTER JOIN #Dep dep ON u.LocalUpdateID = dep.LocalID
    LEFT OUTER JOIN #Dead dead ON u.LocalUpdateID = dead.LocalID
    WHERE
    p.PublicationState = 1
    AND (p.ExplicitlyDeployable = 1 OR p.UpdateType IN ('Category', 'Detectoid'))
    AND p.ReceivedFromCreatorService <= @minimumDeadDeploymentTime
    AND b.localID IS NULL
    AND pr.LocalID IS NULL
    AND Dep.LocalID IS NULL
    AND dead.LocalID IS NULL
    ORDER BY u.LocalUpdateID DESC

    DROP TABLE #Bundles
    DROP TABLE #PreReq
    DROP TABLE #Dep
    DROP TABLE #Dead

    1 person found this answer helpful.
    0 comments No comments

  4. Rita Hu -MSFT 9,646 Reputation points
    2021-12-20T02:34:55.57+00:00

    @Horatio Rooks
    Thanks for your posting on Q&A.

    I found a related link for your reference. Please follow this link to check whether it is helpful.

    Note that the link is not from MS, just for your reference.

    Please keep us in touch if there are any updates of the case.

    Regards,
    Rita


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  5. Frank Cedeno 6 Reputation points
    2022-07-13T15:36:08.027+00:00

    If you are confident in tsql you can update the sql script for spGetObsoleteUpdatesToCleanup and comment out the Select Statement (the whole select statement but only the select statement. That is starting in "Select Distinct" and ending in Order By u.LocalUpdateID DESC") immediately under what you commented out and before the "RETURN(0)" statement, paste the code below:

    CREATE TABLE #Bundles (localID INT PRIMARY KEY)
    INSERT #Bundles SELECT r1.LocalUpdateID FROM dbo.tbBundleDependency bd
    INNER JOIN dbo.tbRevision r1 ON bd.BundledRevisionID = r1.RevisionID GROUP BY r1.LocalUpdateID

    CREATE TABLE #PreReq (LocalID INT PRIMARY KEY)
    INSERT #PreReq SELECT r2.LocalUpdateID FROM dbo.tbPrerequisiteDependency pd
    INNER JOIN dbo.tbRevision r2 ON pd.PrerequisiteRevisionID = r2.RevisionID GROUP BY r2.LocalUpdateID

    CREATE TABLE #Dep (LocalID INT PRIMARY KEY)
    INSERT #Dep SELECT r3.LocalUpdateID FROM dbo.tbDeployment d
    INNER JOIN dbo.tbRevision r3 ON d.RevisionID = r3.RevisionID
    WHERE d.TargetGroupTypeID = 0
    AND d.ActionID IN (0, 1, 3) GROUP BY r3.LocalUpdateID
    CREATE TABLE #Dead (LocalID INT PRIMARY KEY)
    INSERT #Dead SELECT r4.LocalUpdateID FROM dbo.tbDeadDeployment dd
    INNER JOIN dbo.tbRevision r4 ON dd.RevisionID = r4.RevisionID
    WHERE dd.TargetGroupTypeID = 0
    AND dd.ActionID IN (0, 1, 3)
    AND dd.TimeOfDeath > @minimumDeadDeploymentTime
    GROUP BY r4.LocalUpdateID

    SELECT DISTINCT u.LocalUpdateID FROM dbo.tbUpdate u
    INNER JOIN dbo.tbRevision r ON r.LocalUpdateID = u.LocalUpdateID
    INNER JOIN dbo.tbProperty p ON p.RevisionID = r.RevisionID
    LEFT OUTER JOIN #Bundles b ON b.localID = u.LocalUpdateID
    LEFT OUTER JOIN #PreReq pr ON u.localUpdateID = pr.localID
    LEFT OUTER JOIN #Dep dep ON u.LocalUpdateID = dep.LocalID
    LEFT OUTER JOIN #Dead dead ON u.LocalUpdateID = dead.LocalID
    WHERE
    p.PublicationState = 1
    AND (p.ExplicitlyDeployable = 1 OR p.UpdateType IN ('Category', 'Detectoid'))
    AND p.ReceivedFromCreatorService <= @minimumDeadDeploymentTime
    AND b.localID IS NULL
    AND pr.LocalID IS NULL
    AND Dep.LocalID IS NULL
    AND dead.LocalID IS NULL
    ORDER BY u.LocalUpdateID DESC

    DROP TABLE #Bundles
    DROP TABLE #PreReq
    DROP TABLE #Dep
    DROP TABLE #Dead

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.