Keeping Windows Server Update Services (WSUS) Tidy

Keeping WSUS running at it’s best requires more effort than is commonly understood and without proper maintenance you might eventually start seeing nasty errors from the console.


I’ll try to run through a few of the solutions I’ve discovered and how you can use them together to keep things running smoothly. You can also use these processes to keep WSUS managed by ConfigMgr tidy 🙂

Categories and Products

An important first step to take, is to only pick the categories and products that apply to your estate, before running your initial sync. Then occasionally it’s worth revisiting the list to ensure it is still relevant. Getting this right will save you headaches later on.

WSUS Database

The second thing to consider is re-indexing and de-fragmenting the database. Available on the MS TechNet Gallery, is a SQL script which does this job perfectly. However, running this script can be a bit tricky if you accepted the default ‘WID’ (Windows Internal Database) option during setup.

  1. Download SQL Management Studio – here
  2. Download the maintenance script – here
  3. Determine whether your WSUS database is WID or SQL:
    • Server 2008 R2 – Open Server Manager then on the right column select ‘Windows Server Update Services’ and then view services. If ‘Windows Internal Database (MICROSOFT##SSEE)’ is present, the instance is using WID.
    • Server 2012 R2 – Open Server Manager, then on the right column select ‘WSUS’ and then scroll right down to the bottom to see the roles and features installed. If you see ‘WID Database’ instead of just ‘Database’, your instance is using WID.
  4. If you are running SQL skip to step 8.
  5. If you are running WID, you will need to install the SQL Management Studio, downloaded earlier, on the server running WSUS.
  6. Now you will need to logon to the server as the builtin administrator account, or the account that installed WSUS.
  7. Open SQL Management Studio and connect to one of the following:
    • Server 2008 R2 – \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
    • Server 2012 R2 – \\.\pipe\MICROSOFT##WID\tsql\query
  8. Once connected, navigate to the SUSDB database an run a new query.
  9. Paste the code from the downloaded maintenance script into the new query window and run the query.

This script can be ran as a monthly task using a SQL job or if you are using WID, a batch cmd that calls SQLCMD, using task scheduler. I’ll look into the exact command you need later.

Decline Superseded Updates

If you do not regularly decline superseded updates, over time disk space on the server will quickly use up and the console will become unresponsive. You can of course manually go through the console and decline superseded updates, but why would you do that if PowerShell can do it for you? In a previous post, I looked into a script which connects to the WSUS API and declines superseded updates. I’ve improved the script slightly, adopting some ideas from a script published on the MS Technet Gallery and have uploaded it to GitHub.

Again, this can be run on a regular basis using as a scheduled task.


An issue I came across while going through this process for a client, was that the API call kept timing out. This was because they had left their WSUS environment running for years without maintenance. I tried for a long while to figure out how I could extend the time out period etc but couldn’t come up with anything. I tested the below process for getting round this successfully:

  • Ensure you have a backup of the SUSDB then run a re-index.
  • When that completes, run the following stored procedure in SQL Server Management Studio or SQL Server Management Studio Express.

DECLARE @msg nvarchar(100)
CREATE TABLE #results (Col1 INT)
INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup
SELECT Col1 FROM #results
INTO @var1
BEGIN SET @msg = ‘Deleting’ + CONVERT(varchar(10), @var1)
RAISERROR(@msg,0,1) WITH NOWAIT EXEC spDeleteUpdate @localUpdateID=@var1
DROP TABLE #results

This will run stored procedures called spGetObsoleteUpdates and spDeleteUpdate which remove unused updates and update revisions. After that finishes, try running the maintenance script again. If that fails, unfortunately you are left with reinstalling WSUS with a fresh database.

WSUS Cleanup

The following script performs the tasks completed by the inbuilt wizard and is maintained here:

$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer();
$cleanupScope = new-object Microsoft.UpdateServices.Administration.CleanupScope;
$cleanupScope.DeclineSupersededUpdates = $true
$cleanupScope.DeclineExpiredUpdates = $true
$cleanupScope.CleanupObsoleteUpdates = $true
$cleanupScope.CompressUpdates = $true
$cleanupScope.CleanupUnneededContentFiles = $true
$cleanupManager = $wsus.GetCleanupManager();

Putting it all together

To automate the solution using task scheduler, create a folder such as C:\WSUSMaint and copy the two powershell scripts and sql scripts to that location. Then in task scheduler, create three tasks with the following actions:

  • WSUS Cleanup action – powershell -file C:\WSUSMaint\wsus_clean.ps1
  • SUSDB Re-index action – “C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.exe” -S \\.\pipe\Microsoft##WID\tsql\query -i C:\WSUSMaint\SUSDBMaint.sql
    • In the above sql script substitute “\\.\pipe\Microsoft##WID\tsql\query” with “\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query” if you are on Server 2008r2
  • WSUS Decline action – powershell -file C:\WSUSMaint\wsus_decline.ps1

It is recommended that you run these when there is no chance of a collide with a WSUS sync as this can cause problems where it will re-sync the updates you are trying to clear. Here is an example schedule for your three tasks:

  • Run wsus_Decline task
  • Wait 1 hour
  • Run wsus_Clean task
  • Wait 1 hour
  • Run SUSBMaint.sql task

I hope this all makes sense and if you have a question, leave a comment.

Keeping Windows Server Update Services (WSUS) Tidy

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s