Some WSUS Maintenance

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 \\.\pipe\Microsoft##WID\tsql\query.

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

General Cleanup

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.

I decided to try the PowerShell-based maintenance utility (homepage, GitHub), but that is also timing out. At least it continues with other operations:

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:

Invoke-WsusServerCleanup -CompressUpdates

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. 

Reset Client

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.