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/
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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/
Yes, but the other items related to the WSUS maintenance are almost more important than the server cleanup wizard (SCW) alone.
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
@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.
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