I use WSUS at a small-ish site (14 clients). The WSUS server runs on the same machine as Server 2016 Essentials. For quite a while, the server has not been reporting to itself; it keeps getting updates directly from Microsoft. Lately, WSUS has been increasingly unresponsive, often giving up with the familiar “Database Error”:
Extend Connection Timeout
You’ll need SQL Server Management Studio (SSMS). Start that as administrator and connect to
To reduce the database errors, I followed the second option in this post to extend the SQL connection timeout:
Restart the SQL server to make that change take effect.
Delete Obsolete Updates
The previous article also mentions the spGetObsoleteUpdatesToCleanup stored procedure to count obsolete update. I have over 27,000. This article is a gem for fixing that. I’m copying scripts verbatim to make sure I can hang on to them:
1. First, create two indexes. This brought down execution time for spGetObsoleteUpdatesToCleanup from 39 minutes to a few seconds:
USE [SUSDB] GO CREATE NONCLUSTERED INDEX [IX_tbRevisionSupersedesUpdate] ON [dbo].[tbRevisionSupersedesUpdate]([SupersededUpdateID]) GO CREATE NONCLUSTERED INDEX [IX_tbLocalizedPropertyForRevision] ON [dbo].[tbLocalizedPropertyForRevision]([LocalizedPropertyID]) GO
2. Second, stop the WSUS service and run this script to delete obsolete updates. It took about 19 hours to delete the 27,000 obsolete updates.
USE SUSDB DECLARE @var1 INT, @curitem INT, @totaltodelete INT DECLARE @msg nvarchar(200) CREATE TABLE #results (Col1 INT) INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup SET @totaltodelete = (SELECT COUNT(*) FROM #results) SELECT @curitem=1 DECLARE WC Cursor FOR SELECT Col1 FROM #results OPEN WC FETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1) BEGIN SET @msg = cast(@curitem as varchar(5)) + '/' + cast(@totaltodelete as varchar(5)) + ': Deleting ' + CONVERT(varchar(10), @var1) + ' ' + cast(getdate() as varchar(30)) RAISERROR(@msg,0,1) WITH NOWAIT EXEC spDeleteUpdate @localUpdateID=@var1 SET @curitem = @curitem +1 IF @curitem < 50000 FETCH NEXT FROM WC INTO @var1 END CLOSE WC DEALLOCATE WC DROP TABLE #results
After that finished, I started WSUS and ran a “normal” Cleanup Wizard a couple times. Unfortunately this is still aborting (throwing the Database Error message) while “Deleting unused update revisions.” I tried extending the SQL timeout to 3600 seconds (an hour), but I was still getting the Database Error.
Okay, this serverfault question has an idea I haven’t tried yet: extending the timeout of the WSUS Administration site in IIS. Seems like that probably related to the UI, but who knows, maybe it affects what PowerShell is doing as well. Let’s set that to 3600 as well and re-run the script:
This time it—fairly quickly—made it past CleanuupUnneededContentFiles but still timed out on CompressUpdates:
That same error happened two more times. I guess I’ll just keep re-running the script or the Cleanup Wizard and see if I can get it to complete without timing out.
So I’m wondering if I need to go in an decline and remove the 6500+ updates that WSUS knows I don’t need but is still holding for approval:
It’s probably best to make sure I’m only declining updates that are either Installed or Not Applicable:
After doing that, the script is still timing out on the CompressUpdates step. Interesting that the CleanupObsoleteUpdates step reports that it deleted 0 updates even after declining all those updates.
I found I can re-run just the failing process directly in PowerShell:
This thread and others say just keep running it until it works. Once it is finally cleaned up, I’ll be running this script as a daily scheduled task in the hopes that it will not get so far out of whack.
Update June 28, 2020 To my surprise, CompressUpdates did finally start succeeding yesterday, possibly after I tried (and failed) to shrink the SUSDB database using SSMS.
A Paid Alternative
Wow, I just found The complete guide to Microsoft WSUS and Configuration Manager SUP maintenance at the Microsoft site. Holy moly, that’s a lot of work. No wonder Adam Marshall refers to that page as a selling point for the WSUS Automated Maintenance (WAM) utility that he sells. It seems like WSUS should be able to handle a site with 14 computers with only the default settings and tools. Since it doesn’t, Adam’s tool could be worth the price.
Finally, to try to resolve the problem of the server not reporting to itself, I reset the client using this PowerShell script from this page:
Remove-ItemProperty -Name AccountDomainSid, PingID, SusClientId, SusClientIDValidation -Path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\ -ErrorAction SilentlyContinue Remove-Item "$env:SystemRoot\SoftwareDistribution\" -Recurse -Force -ErrorAction SilentlyContinue Start-Service -Name BITS, wuauserv wuauclt /resetauthorization /detectnow (New-Object -ComObject Microsoft.Update.AutoUpdate).DetectNow()
Unfortunately, the server is still not reporting to itself. I’ll have to come back to this another time.