Share via

SCCM/WSUS – Clean-up WSUS Strategy & Maintenance

  • Article

(Note: Ensure you take a backup of your WSUS database before you proceed any further)

One of the most overlooked exercises when it comes to maintaining our WSUS environment is having a steady clean-up process.

Over time with schedules such as Patch Tuesdays or any regular patching intervals or aggressive synchronizations of updates, classifications and categories which are not needed, several of these things can happen.

  • Huge amount of Obsolete Updates
  • Huge WSUS Database
  • Slowed down performance of patching
  • WSUS Console cannot be accessed

These can cause huge issues within your environments if little to no clean-ups have actually been ran.

I’ve faced this at many customer sites and have created a couple of scripts for an overall solution strategy on how to keep everything clean.

Prerequisite – Using SQL instance rather than WID

I would recommend that you are using a SQL instance rather than a Windows Internal Database (WID). If needing to convert them here is a guide on how to do this

/en-us/windows-server/administration/windows-server-update-services/manage/wid-to-sql-migration

Removal of Obsolete Updates

This particular part is what can usually kill your processes and resources on your server as well as prevent you from accessing the WSUS console and causing some horrible logs in your WSUSCTRL.log files.

And if this gets huge to the point that it’s in thousands, then the clean-up wizard will not work at all as it tends to get stuck or come to a halt and internally crashing the application, which then causes a SQL deadlock on the WSUS database which can halt all WSUS processes.

So the first thing we want to do is identify how many obsolete updates exist, so please perform the following;

  • Open SQL Management Studios
  • Connect to the SQL Database server with the WSUS Database
  • Open a new query
  • Type execute spGetObsoleteUpdatestocleanup

This should give you a list of every single update which is not used or needed. And if this is in several thousands then this will cause a huge bottleneck.

So what we will want to do is to add the list of these updates and add them into a .txt file for now so that we can format a list of commands to run as a SQL query to get rid of them.

Why individually and not a command to purge: There are commands to purge all of the updates, the reason I choose this method to delete each one individually is because this allows you to have a marker as to where it stops. With several thousands of these obsolete updates it can take an extremely long time to get rid of them, and it tends to get to a point after its gone through a fair amount of water weight of updates, it gets to a point where the progress slows up and then you won’t know how many are left, this allows you to see the exact ones you have by KB Article numbers and remove accordingly)

So once we create the text fie we’ll want to output them as commands to remove them via a SQL query so below are some PowerShell commands to perform this.

$CSV = Import-CSV “<Location>\Updates.txt” -Header Updates
 
$CSV | ForEach-Object {“exec SPDeleteUpdate @localUpdateID=” + $_.Updates} | Out-File <Location>\DeleteUpdates.txt

 

This will take the text file you had created and concatenate each update with a command to remove them.

Once done copy everything from that text file and paste into a query in the SQL management studios so that it can begin.

Why not create a PowerShell Script to connect to the SQL Database and run: With the clean-up of WSUS it can be quite a sensitive structure, and though this process can be automated (I have the scripts and Orchestrator runbook for it) there are some things I think shouldn’t be automated, at best perhaps semi-automated. Cleaning obsolete updates is or can be a lengthy process and you may not want to trust automation to be responsible for it especially where serious bottle necks can occur

You can open an extra query window to run the “execute spGetObsoleteUpdatesToCleanup” to check its progress, may take a couple of tries whilst its busy.

Once this does eventually complete, then run the command again above, once this shows s 0 then do the following;

1.      Open the WSUS Console

2.      Go to Administration – WSUS Clean-up Wizard

3.      Run the Clean Obsolete Updates Option. This should go through quick-ish as well as cleaning up any update revisions.

4.      Go through the clean-up wizard and tick each option to run individually. Once this has been done then run the whole clean-up wizard until it completes.

5.      Then run the WSUS Re-Indexing script which can be found here - https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61

6.      Go back to the SQL Management Studios and connect to the WSUS Database. Then shrink the WSUS database to gain its space back.

Clean-Up Scripts Going Forward

Now that the major clean-up has been completed, we want to prevent this situation from happening again so what we can use is a PowerShell script which I wrote that can go through the clean-up processes.

I would suggest to run this every 2 weeks, or depending how aggressive your schedules are then perhaps every week.

These scripts would be added to a scheduled task which would run on this interval.

So, we have two scripts

Windows Server 2012 & Higher

This script is for Server 2012 & higher and will go through each clean-up step and produce a log file with a location of your choice with appropriate time stamping. You can run this ad-hoc or attach to a Scheduled Task

# Create Log File
 
$Timestamp = Get-Date -format dd-MM-yyyy-HH-mm-ss
 
$WeekStamp = Get-Date -UFormat %V
 
$LogFile = "<Location>\WSUSCleanup$timestamp.txt"
 
  
 
# Get Current WSUS Server
 
Add-Content $LogFile "Script Started at $Timestamp"
 
$wsus = Get-WSUSServer
 
Add-Content $LogFile "Current WSUS Server is $wsus"
 
  
 
# Cleanup Steps
 
Add-Content $LogFile ""
 
Add-Content $LogFile "Starting cleanup steps"
 
Add-Content $LogFile "Cleanup Obsolete Updates"
 
$WSUS | Invoke-WsusServerCleanup -CleanupObsoleteUpdates | Add-Content $Logfile
 
  
 
Add-Content ""
 
Add-Content $LogFile "Cleanup Obsolete computers"
 
$WSUS | Invoke-WsusServerCleanup -CleanupObsoleteComputers | Add-Content $Logfile
 
  
 
Add-Content ""
 
Add-Content $LogFile "Cleanup Unneeded Content Files"
 
$WSUS | Invoke-WsusServerCleanup -CleanupUnneededContentFiles | Add-Content $Logfile
 
  
 
Add-Content ""
 
Add-Content $LogFile "Declined Expired Updates"
 
$WSUS | Invoke-WsusServerCleanup -DeclineExpiredUpdates | Add-Content $Logfile
 
  
 
Add-Content ""
 
Add-Content $LogFile "Declined Superseded Updates"
 
$WSUS | Invoke-WsusServerCleanup -DeclineSupersededUpdates | Add-Content $Logfile

 

 

Windows Server 2008

Though the PowerShell modules can be upgraded and installed, by default the WSUS role doesn’t have a PowerShell module specifically for it, so as a workaround I had done another script which is suitable for this version.

Same as the script above which can be run ad-hoc and also be attached to a scheduled task.

# Create Log File
 
$Timestamp = Get-Date -format dd-MM-yyyy-HH-mm-ss
 
$WeekStamp = Get-Date -UFormat %V
 
$LogFile = "<Location>WSUSCleanup$timestamp.txt"
 
  
 
# Get Current WSUS Server
 
Add-Content $LogFile "Script Started at $Timestamp"
 
#$wsus = Get-WSUSServer
 
[void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")
 
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer('FNEGLWSUS01',$False,"8530")
 
Add-Content $LogFile Current WSUS Server is $wsus.name
 
  
 
# Cleanup Module
 
$cleanupscope = New-Object Microsoft.UpdateServices.Administration.Cleanupscope
 
  
 
# Cleanup Obsolete Updates
 
$cleanupscope.CleanupObsoleteUpdates = $true
 
Add-Content $LogFile ""
 
Add-Content $LogFile "Starting cleanup steps"
 
Add-Content $LogFile "Cleanup Obsolete Updates"
 
$cleanupManager = $wsus.GetCleanupManager()
 
$CleanupManager.PerformCleanup($cleanupscope) | Out-String | Add-Content $LogFile
 
  
 
# Cleanup Obsolete Computers
 
$cleanupscope.CleanupObsoleteComputers = $true
 
$cleanupscope.CleanupObsoleteUpdates = $false
 
Add-Content $LogFile ""
 
Add-Content $LogFile "Cleanup Obsolete computers"
 
$cleanupManager = $wsus.GetCleanupManager()
 
$CleanupManager.PerformCleanup($cleanupscope) | Out-String | Add-Content $LogFile
 
  
 
# Cleanup Content Files
 
$cleanupscope.CleanupObsoleteComputers = $false
 
$cleanupscope.CleanupObsoleteUpdates = $false
 
$cleanupscope.CompressUpdates = $true
 
Add-Content $LogFile ""
 
Add-Content $LogFile "Cleanup Unneeded Content Files"
 
$cleanupManager = $wsus.GetCleanupManager()
 
$CleanupManager.PerformCleanup($cleanupscope) | Out-String | Add-Content $LogFile
 
  
 
  
 
# Cleanup Declined Updates
 
$cleanupscope.CleanupObsoleteComputers = $false
 
$cleanupscope.CleanupObsoleteUpdates = $false
 
$cleanupscope.CompressUpdates = $false
 
$cleanupscope.DeclinedExpiredUpdates = $true
 
Add-Content $LogFile ""
 
Add-Content $LogFile "Declined Expired Updates"
 
$cleanupManager = $wsus.GetCleanupManager()
 
$CleanupManager.PerformCleanup($cleanupscope) | Out-String | Add-Content $LogFile
 
  
 
# Declined Superseded Updates
 
$cleanupscope.CleanupObsoleteComputers = $false
 
$cleanupscope.CleanupObsoleteUpdates = $false
 
$cleanupscope.CompressUpdates = $false
 
$cleanupscope.DeclinedExpiredUpdates = $false
 
$cleanupscope.DeclinedSupersededUpdates = $true
 
Add-Content $LogFile ""
 
Add-Content $LogFile "Declined Superseded Updates"
 
$cleanupManager = $wsus.GetCleanupManager()
 
$CleanupManager.PerformCleanup($cleanupscope) | Out-String | Add-Content $LogFile
 
$cleanupscope.DeclinedSupersededUpdates = $false

** **

Future Reference

Whilst the scripts or scheduled tasks do run on intervals, you may want to use the Re-Indexing script perhaps once a month afterwards.