Software update point and WSUS maintenance: cleanup, reindexing and supersedence

A neglected WSUS database is behind a surprising number of Configuration Manager patching problems: slow synchronisations, scan timeouts and HTTP 503 errors on clients. In this post I'll switch on the maintenance Configuration Manager can do for you, schedule the two jobs it doesn't do (backup and reindex), and show how to prove each one worked.

How it works#

Your software update point (SUP) is a WSUS server controlled by Configuration Manager. Every client scan is evaluated against the WSUS catalogue, so every update that isn't declined adds to the work. Microsoft's WSUS maintenance guide warns that more than about 1,500 superseded-but-not-declined updates can cause problems on both servers and clients.

Since version 1906, the site can run three WSUS tasks after every synchronisation. The rest is still your job:

Three logs on the site server tell the story: wsyncmgr.log (synchronisation and cleanup), WCM.log (SUP configuration and subscribed products, classifications and languages) and WSUSCtrl.log (WSUS configuration, database connectivity and health).

Prerequisites#

• Rights to change site component settings at the top-level site (CAS or standalone primary).
• If SUSDB is on a remote SQL Server: for index creation, the WSUS Server Connection Account (or, if none is set, the site server's computer account) needs CONNECT SQL plus sysadmin, or db_ddladmin and db_owner. For removing obsolete updates, the site server's computer account needs CONNECT SQL plus db_datareader and db_datawriter.
• Know where SUSDB lives. If the value below contains ##WID (or ##SSEE on very old servers), it's the Windows Internal Database (WID):

Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Update Services\Server\Setup' -Name SQLServerName

Step-by-step#

1. Set supersedence rules#

Go to Administration › Overview › Site Configuration › Sites, select the top-level site, then Configure Site Components › Software Update Point. On the Supersedence Rules tab, choose whether superseded updates expire immediately or after a number of months, separately for feature updates and other updates. The month count starts from the creation date of the superseding update. Pick a value that gives you time to finish deploying the replacement — expired updates can't be deployed.

2. Turn on the WSUS Maintenance options#

In the same dialog, on the WSUS Maintenance tab, select:

• Decline expired updates in WSUS according to supersedence rules
• Add non-clustered indexes to the WSUS database
• Remove obsolete updates from the WSUS database

They take effect after the next synchronisation and then run after every sync. Declines apply to the WSUS servers at the CAS, primary and secondary sites.

3. Schedule backup and reindex#

Microsoft publishes a T-SQL script, Reindex the WSUS database, that defragments fragmented indexes, sets a fill factor on busy tables and updates statistics. Copy it from the reference below into C:\WSUS\SUSDBMaint.sql unchanged.

For WID, run it with the sqlcmd utility from an elevated prompt (or a scheduled task running as an account with rights to SUSDB):

sqlcmd -S \\.\pipe\MICROSOFT##WID\tsql\query -i C:\WSUS\SUSDBMaint.sql -o C:\WSUS\reindexout.txt

For SUSDB on full SQL Server, create a maintenance plan with a backup step followed by an Execute T-SQL Statement task containing the script. Either way, run it monthly and before your sync schedule, so Configuration Manager's post-sync cleanup works on a freshly indexed database.

4. Tune the WsusPool application pool#

WSUS caches update metadata in memory, and when the pool recycles under load clients receive HTTP 503 (0x80244022). In IIS Manager › Application Pools › WsusPool › Advanced Settings:

• Increase Queue Length above the default.
• Set Private Memory Limit and Virtual Memory Limit to 0, and make sure the server has the RAM to support it.
• Disable the 29-hour Regular Time Interval recycle, Ping and Idle Time-out.

5. First clean-up of a neglected server#

If WSUS has never been maintained, obsolete-update removal can time out at its 30-minute limit run after run. Back up and reindex first, then run the WSUS console's Options › Server Cleanup Wizard with only Unused updates and update revisions selected, repeating until it completes. Microsoft's maintenance guide includes a SQL alternative for servers where the wizard keeps timing out.

Verify#

1. After the next sync, open wsyncmgr.log and look for these entries:
   Text

   Cleanup processed <number> total updates and declined <number>
   Calling WSUS Cleanup.
   Successfully completed WSUS Cleanup.
   Deleting old expired updates...
   Deleted <number> expired updates total

2. Count superseded updates that are still active in SUSDB. The number should fall sharply after the first cleanup and stay low:
   Text

   SELECT COUNT(UpdateID) FROM vwMinimalUpdate WHERE IsSuperseded = 1 AND Declined = 0

3. Check reindexout.txt: it lists any ALTER INDEX commands the script ran and ends with Done updating statistics.
4. Confirm Monitoring › Software Update Point Synchronization Status shows a successful sync, and that clients log Successfully completed scan. in WUAHandler.log.

Tips & gotchas#

• Don't sync during manual maintenance. Set the sync schedule to manual while you work, or downstream servers may pull back what you just removed.
• Order matters in a hierarchy. Manual cleanup runs bottom-up and declines run top-down; Configuration Manager's built-in cleanup handles this after each sync.
• Need a declined update back? Approve it in the WSUS console, change it back to Not approved, then resynchronise the SUP.
• Subscribe only to what you deploy. Every extra product or classification grows SUSDB; WCM.log records each subscription change.
• If clients still fail to scan after maintenance, work through the client side in updates not installing: WUAHandler.log and UpdatesDeployment.log.

References#

• Software updates maintenance (Configuration Manager)
• The complete guide to WSUS and Configuration Manager SUP maintenance
• Reindex the WSUS database