Cleaning up a WSUS database

We thought it was a good idea to include drivers in our WSUS. Well, it wasn't… WSUS downloaded tens of thousands of drivers which caused the database to grow larger than 10 GB (we're using MSSQL Express instead of WID). This made our primary WSUS and a downstream server stop working. The cleanup wizard wasn't working anymore, because MSSQL Express didn't allow to do anything with the database. Shrinking the database using SQL Management Studio didn't work either.

I know it is pretty easy to setup just a new WSUS, but I didn't want to lose all the settings and the approved updates. So I tried to find a way to cleanup the database. The downstream was not that problem, because it was a replica.

First thing was stop WSUS and IIS and to copy the database to another server where a licensed MSSQL was running. I attached it and gave the computer account (like mycompany.local\my-wsus$) the db_owner and the webService roles. Then I edited the following registry value to point to the other MSSQL server:


After restarting the WSUS and IIS services, I was able to start the WSUS MMC and to see all the settings. So I had a working WSUS again and now could try to remove the drivers. Of course I turned off the setting that made WSUS download drivers ;)

Just to decline the drivers didn't work. The metadata was still in the database. Luckily, I found a german article providing some PowerShell snippets that actually delete the metadata from the database.

First I initialized the connection to the WSUS server:

[String]$WsusServer = "my-wsus"
[Boolean]$UseSSL    = $False
[Int32]$PortNumber  = 8530
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer($WsusServer,$UseSSL,$PortNumber)

Now I could delete the drivers from the database (note that even on a german operating system, I had to use the english term "Drivers"):

$wsus.GetUpdates() | 
Where {$_.UpdateClassificationTitle -eq "Drivers"} | 
ForEach-Object {$wsus.DeleteUpdate($_.Id.UpdateID);Write-Host $_.Title "is removed."}

This removed all the metadata of the drivers from the database. This removed nearly 3 GB of data from the database and I was able to shrink it.

I gained another GB by removing the language packs I accidentally downloaded some months ago:

$wsus.GetUpdates() | 
Where {$_.Title.contains("Language Pack")} |
ForEach-Object {$wsus.DeleteUpdate($_.Id.UpdateID);Write-Host $_.Title "is removed."}

I repeated the step above with the terms Language Interface Pack and Language Features.

After that, the database was down to 7 GB and I was able to attach it to MSSQL Express.

As a last step, I reset the replica by first removing the database and the repository and then running wsusutil:

wsusutil postinstall SQL_INSTANCE_NAME=my-replica\sqlexpress CONTENT_DIR=D:\WSUS

I did it :) Both the primary WSUS and the replica were working again!

Published on 2022-12-23, 15:56 +0000