The complete guide to Microsoft WSUS and Configuration Manager SUP maintenance
~ Meghan Stewart | Support Escalation Engineer
I’ve recently seen a lot of questions about Windows Server Update Services (WSUS) maintenance for Configuration Manager environments so I wanted to take a minute and hopefully address some of them here. Usually the questions are along the lines of “How should I properly run this in a Configuration Manager environment?”, or “How often should I be running this maintenance?” I have also seen extremely conscientious Configuration Manager administrators be completely unaware that WSUS maintenance should be run at all. After all, most of us just setup WSUS servers since it is a prerequisite for a Software Update Point (SUP). Once the SUP is setup, we close the WSUS console and pretend it doesn’t exist anymore. Unfortunately, this can be problematic for our Configuration Manager clients and the overall performance of the WSUS/SUP server.
So with the understanding that this maintenance needs to be done, I bet you’re wondering what maintenance you need to do and how often you need to be doing it. The answer is you should be doing this maintenance monthly and I’ll show you how below. Running the proper maintenance is pretty easy and doesn’t take very long for WSUS machines that have been well maintained from the beginning, however be aware that if you have never run WSUS maintenance before and the WSUS computer has been in production for a while, the cleanup may be harder the first time you run it, but will be much faster in the subsequent months.
Before we get started, it’s important that I mention a few things:
You should read all of the instructions before starting this process, as you may realize that you need to do steps in the middle of the article before you are able to go through the process from the start of the article to the end.
Remember that when doing WSUS maintenance when you have downstream servers, you add to the WSUS servers from the top down, but remove from the bottom up. So if you are syncing/adding updates, they flow into the top (upstream WSUS server) then replicate down to the downstream servers. When you do a cleanup, you are removing things from the WSUS servers, so you should remove from the bottom of the hierarchy and allow the changes to flow up to the top.
It’s important to note that this WSUS maintenance can be performed simultaneously on multiple servers in the same tier. You do however want to make sure that one tier is done before moving onto the next one when doing a cleanup. The cleanup and re-index steps I talk about below should be run on all WSUS servers regardless of whether they are a replica WSUS server or not (see section 4 below for how to determine if the WSUS is a replica).
This is a big one. You must ensure that you do not sync your SUPs during this maintenance process as it is possible you will lose some of the work you have already done if you do. You may want to check your SUP sync schedule and set it to manual during this process.
Note that If you have multiple SUPs off the primary site or CAS that do not share the SUSDB, consider the WSUS that syncs with the first SUP on the site as residing in a tier below the site. For example, my CAS site has 2 SUPs. The one named “New” syncs with Microsoft update. This would be my top tier (Tier1). The server named “2012” syncs with “New” and it would be considered in the second tier and can be cleaned up at the same time I would do all my other Tier2 servers, such as my primary site’s single SUP.
How to run WSUS maintenance
The four basic steps necessary for proper WSUS maintenance include the following:
- Backup the WSUS database
- Run the WSUS Server Cleanup Wizard
- Re-index the WSUS database
- Decline superseded updates
I go through each of these below.
1. Backup your WSUS database
Backup your WSUS database (SUSDB) using whichever method is your favorite.
2. Run the WSUS Server Cleanup Wizard
The WSUS Server Cleanup Wizard can be launched from the console. It is located under Options as shown here:
NOTE If you have not done maintenance before, run step 3, then 2, then 3 again. The initial re-index will help the cleanup go faster.
Please be aware that if the WSUS Server Cleanup Wizard has never been run and the WSUS has been in production for a while, the cleanup may time out. In that case, re-index with Steps 2 and 3 first, then run the cleanup with only the top box checked (unused updates and updates revisions). This may require a few passes. If it times out, run it again until it completes, then run each of the other options one at a time. Lastly make a “full pass” with all options checked. See the following TechNet documentation for more information:
The cleanup is finished once it actually reports the number of items it has removed. If you do not see this returned on your WSUS server, it is safe to assume that the cleanup timed out and you will need to start it again.
3. Re-index the WSUS database
After the cleanup is finished, you need to re-index the WSUS database (SUSDB) with the following script:
The steps to run the script are different depending on whether you installed SUSDB on SQL Server or on Windows Internal Database (WID). This was specified when you actually installed SUSDB. If you are not sure which you used, you can check a registry key on the WSUS server located at HKLM\Software\Microsoft\Update Services\Server\Setup to verify. Look for the SQLServerName value. If you see just a server name or server\instance, you are using SQL server. If you see something that has the string ##SSEE or ##WID in it, you installed on Windows Internal Database, as demonstrated below:
If you installed SUSDB on Windows Internal Database
If you installed SUSDB on Windows Internal Database (WID) you will need to install SQL Management Studio Express in order to run the re-index script. If you’re not sure which version of SQL Server Management Studio Express to install, here’s an easy way to figure that out:
- For Windows Server 2012, go to C:\Windows\WID\Log and find the error log that has the version number you’re using. Lookup the version number here:
321185 – How to determine the version, edition and update level of SQL Server and its components (https://support.microsoft.com/en-us/kb/321185)
This will tell you what Service Pack level it is running. Include the SP level when searching the Download Center for SQL Management Studio Express as sometimes it does matter.
- For Windows Server 2008 R2 or below, go to C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\LOG and open up the last error log with Notepad. At the very top there will be a version number (e.g. 9.00.4035.00 x64). Lookup the version number here:
321185 – How to determine the version, edition and update level of SQL Server and its components (https://support.microsoft.com/en-us/kb/321185)
This will tell you what Service Pack level it is running. Include the SP level when searching the Download Center for SQL Management Studio Express.
Once SQL Management Studio Express is installed, launch it and it will prompt you to enter the server name to connect to:
- If your OS is Windows Server 2012, use \\.\pipe\MICROSOFT##WID\tsql\query
- If you are not running Windows Server 2012, enter \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
NOTE For WID, you may want to run SQL Server Management Studio Express as administrator if you were not the person who installed WSUS.
TIP Alternatively, you can also use a utility called sqlcmd to run the script if it is installed. See the following TechNet documentation for more information:
If you installed SUSDB on SQL Server
If you installed on full SQL Server, simply launch SQL Server Management Studio and enter the name of the server (and instance if needed) when prompted.
Running the script
To run the script in either SQL Server Management Studio or SQL Server Management Studio Express, click on the New Query button, paste the script in the window and then click Execute. When it is finished you will see Query executed successfully along with the messages of what indexes were rebuilt.
4. Decline superseded updates
Additionally, you may want to decline superseded updates in the WSUS server so it helps your clients scan more efficiently. Before declining updates, you should ensure that the superseding updates are deployed and that you no longer need the superseded ones. Configuration Manager does have a separate cleanup where it expires updates that are superseded based on criteria that you provide it. For more information about this setting, review the Supersedence Rules heading at these links:
- Configuring Software Updates in Configuration Manager
- Planning for Software Updates in Configuration Manager
You can do this manually in WSUS if you wish, or you can run this PowerShell script. Simply download the script and rename it with a .PS1 extension. Please note that I am providing this script “as is” and it should be fully tested in a lab before being used in production. Microsoft makes no guarantees regarding the use of script in any way.
NOTE You always want to run the script with the –SkipDecline parameter before running the decline so you get a summary of how many superseded updates you are about to decline.
I normally recommend to run the script on the WSUS servers if you choose to expire superseded updates immediately in Configuration Manager. I run this once a quarter in my environment. This should be done on all autonomous WSUS servers in the Configuration Manager/WSUS hierarchy. This does not need to be run on WSUS servers set as replica such as Secondary Site SUPs. If you are unsure, verify the setting on your WSUS.
If you do not expire updates immediately in Configuration Manager, you will need to set an exclusion period that matches your Configuration Manager setting for number of days to expire superseded updates. In this case, it would be 60 days since I specified to wait 2 months in my SUP properties.
Examples on how to run the script using PowerShell running as administrator:
Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -Port 80 -SkipDecline
Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -UseSSL -Port 8351 Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -Port 8530
Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -UseSSL -Port 8351
Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -Port 8530
Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -Port 8530 –ExclusionPeriod 60
Running the script with a –SkipDecline and –ExclusionPeriod 60 to gather information about my WSUS and how many updates I will decline:
Running the script with –ExclusionPeriod 60:
Running the script to decline the rest of the superseded updates:
What if I find out I needed one of those updates I declined?
If you decide you need one of these declined updates in Configuration Manager for some reason, you can get it back in WSUS by right-clicking on the update and selecting Approve. Change the approval to Not Approved and resync your SUP to get the update back in.
If the update is no longer in your WSUS, you can import it from the Microsoft Update Catalog as long as it has not been expired from the catalog.
HELP! My WSUS has been running for years without ever having maintenance done and the cleanup wizard keeps timing out.
There are really two different options you can take here:
1. Reinstall WSUS with a fresh database.
2. 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. After this finishes, follow all of the above instructions for running maintenance. This last step is necessary because the stored procedure here only removes unused updates and update revisions.
DECLARE @var1 INT
DECLARE @msg nvarchar(100)
CREATE TABLE #results (Col1 INT)
INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup
DECLARE WC Cursor
SELECT Col1 FROM #results
FETCH NEXT FROM WC
WHILE (@@FETCH_STATUS > -1)
BEGIN SET @msg = ‘Deleting’ + CONVERT(varchar(10), @var1)
RAISERROR(@msg,0,1) WITH NOWAIT EXEC spDeleteUpdate @localUpdateID=@var1
FETCH NEXT FROM WC INTO @var1 END
DROP TABLE #results
Automating WSUS maintenance
I’m often asked whether these WSUS maintenance tasks can be automated, and the answer is yes, assuming that a few requirements are met first.
1. If you have never run WSUS cleanup, you need to do the first two cleanups manually. Your second manual cleanup should be run 30 days from your first since it takes 30 days for some updates and update revisions to “age out”. There are specific reasons for why you don’t want to automate until after your second cleanup. Your first cleanup will probably run longer than normal so you can’t judge how long this maintenance will normally take, whereas the second cleanup is a much better indicator of what is normal for your machines. This is important because you need to figure out about how long each step takes as a baseline (I also like to add about 30 minutes “wiggle room”) so that you can determine the timing for your schedule.
2. If you have downstream WSUS servers, you will need to run them first, then do the upstream servers.
3. To schedule the re-index of the SUSDB you will need a full version of SQL Server. Windows Internal Database (WID) does not have the capability of scheduling out a maintenance task though SQL Server Management Studio Express. With that said, in cases where WID is used you can use the Task Scheduler with SQLCMD mentioned earlier. If you go this route, it’s important that you DO NOT SYNC YOUR WSUS SERVERS/SUPs during this maintenance period! If you do, it is very possible your downstream servers will just end up resyncing all of the updates you just attempted to clean out. Personally, I schedule this overnight before my AM sync so I have time to check on it before my sync runs.
Links you will need and some you may possibly need:
- The Scripting Guys WSUS Cleanup Script
- The Scripting Guys Re-index SUSDB Script
- If you get an error about Agent XPs being turned off in SQL
- Creating a scheduled task to run a Windows PowerShell Script
Setting up the WSUS Cleanup Task in Task Scheduler
The easiest basic directions and troubleshooting for this step are here but I’ll walk you through the process below.
1. Open Task Scheduler and Select Create a Task. Under the General tab, set the name of the task, the user that you want to run the PowerShell script as (most people use a service account), select Run whether a user is logged on or not, then add a description if you wish.
2. Under the Actions tab, add a new action and specify the program/script you want to run. In this case we need to use PowerShell and point it to the PS1 file we want it to run. I use the script found here. If you would like a log, you can append the last line of the script to read:
$cleanupManager.PerformCleanup($cleanupScope)| Out-File c:\wsus\wsusclean.txt
Note that you will get an FYI/warning in Task Scheduler when you save, but this is OK and can be ignored.
3. Set your schedule under the Triggers tab for once a month on any schedule you wish. Again, you must ensure that you do not sync your WSUS during the entire cleanup and re-index time. This statement really is important enough for me to bold it three times in a single article.
4. Set any other conditions or settings you would like to tweak as well. Note that when you save the task, you may be prompted for credentials of the “run as” user.
5. You can also use these steps to configure the Decline-SupersededUpdates.ps1 script to run every 3 months. I usually set this to run before the other cleanup steps, but only after I have run it manually and ensured it completed successfully. I run at 12:00 AM on the 1st Sunday every 3 months.
Setting up the SUSDB re-index for WID using SQLCMD & Task Scheduler
1. Save the script here as a .sql file (e.g. SUSDBMaint.sql)
2. Create a basic task and give it a name:
3. Schedule this task to start about 30 minutes after you expect your cleanup to finish running. My cleanup is running at 1:00 AM every first Sunday. It takes about 30 minutes to run and I am going to give it an additional 30 minutes before starting my re-index. This means I would schedule this task for every 1st Sunday at 2:00 AM, as shown here:
4. Select the action to Start a program. In the Program/script box type the following, where the file specified after the –i parameter is the path to the SQL script you saved in step 1, and the file specified after the –o parameter is where you would like the log to be placed. Here’s an example of what that might look like:
“C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.exe” -S \\.\pipe\Microsoft##WID\tsql\query -i C:\WSUS\SUSDBMaint.sql -o c:\WSUS\reindexout.txt
5. You will get a warning, similar to the one you got when creating the cleanup task. Click Yes to accept the arguments, then click Finish to apply.
6. You can test the script by forcing it to run and reviewing the log for errors. If you run into issues, the log will tell you why. Usually if it fails, the account running the task does not have appropriate permissions or the WID service is not started.
Setting up a basic Scheduled Maintenance Task in SQL for non-WID SUSDBs
NOTE You must be a sysadmin in SQL to create or manage Maintenance Plans.
1. Open SQL Server Management Studio and connect to your WSUS instance. Expand Management, then right-click on Maintenance Plans and select New Maintenance Plan. Give your plan a name.
2. Click on subplan1 and then ensure your Toolbox is in context:
3. Drag and drop the task Execute T-SQL Statement Task:
4. Right-click on it and select Edit. Copy and paste the WSUS re-index script and click OK:
5. Schedule this task to run about 30 minutes after you expect your cleanup to finish running. My cleanup is running at 1:00 AM every first Sunday. It takes about 30 minutes to run and I am going to give it an additional 30 minutes before starting my re-index. This means I would schedule this task to run every 1st Sunday at 2:00 AM.
6. While you are creating the maintenance plan, you may want to consider adding a backup of the SUSDB into your plan as well. I usually backup first, then re-index. Note this may add additional time to your schedule.
Putting it all together
When running this in a hierarchy, you want the WSUS cleanup run from the bottom of the hierarchy up, but you want the decline script to run from the top down.
Since I can’t sync during the actual cleanup, I would prefer to be able to complete all tasks overnight then check on their completion via the logging when I come into the office in the morning before my next sync is scheduled. This is because in the case that something failed, I can reschedule the maintenance for the next night once I identify what failed and resolve the issue.
These tasks may run faster or slower in your environment and the timing of your schedule should reflect that. Hopefully they are faster since my lab environment tends to be a bit slower than a normal production environment. I am a bit aggressive on the timing of the decline scripts since if Tier2 overlaps Tier3 by a few minutes, it will not cause a problem.
My sync is not scheduled to run. This keeps the declines from accidentally flowing into my Tier3 replica WSUS servers from Tier2. I did give myself extra time between the Tier3 decline and the Tier3 cleanup since I definitely want to make sure the decline script finishes before running my cleanup.
This brings up a common question: Since I am not syncing, why shouldn’t I run all of the cleanups and re-indexes at the same time? The answer is that you probably could, but I wouldn’t. If my coworker across the globe needs to run a sync, with this schedule I would minimize the risk of orphaned updates in WSUS and I can schedule it to rerun to completion the next night:
Tier3 WSUS Cleanup
Tier2 WSUS Cleanup
If you’d like more information regarding SUP maintenance in Microsoft Configuration Manager, we also have this KB article: