The complete guide to Microsoft WSUS and Configuration Manager SUP maintenance

Applies to: Windows ServersWindows Server Update ServicesSystem Center Configuration Manager

Introduction


Meghan Stewart | Microsoft Support Escalation Engineer
Eric Ellis | Microsoft Senior Support Escalation Engineer 

In support, we field many questions about Windows Server Update Services (WSUS) maintenance for Configuration Manager environments, so we’re writing to address some of them here.

Questions are often along the lines of “How should I properly run this in a Configuration Manager environment?”, or “How often should I be running this maintenance?” It is not uncommon for extremely conscientious Configuration Manager administrators be completely unaware that WSUS maintenance should be run at all. Most of us just setup WSUS servers because 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. Unfortunately, this can be problematic for Configuration Manager clients, and the overall performance of the WSUS/SUP server.

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 that you should be performing monthly maintenance monthly. Maintenance is pretty easy and doesn’t take long for WSUS servers that have been well maintained from the start, but if it has been some time since WSUS maintenance was done, the cleanup may be more difficult/time consuming the first time. It will be much easier/faster in subsequent months.

Important Considerations


  1. Read all of the information and instructions in this article before starting the maintenance process.
  2. With WSUS where downstream servers are involved, WSUS servers are added from the top down, but removed from the bottom up. When syncing/adding updates, they go to the upstream WSUS server first, then replicate down to the downstream servers. When performing a cleanup and removing items from WSUS servers, start at the bottom of the hierarchy, and allow the changes to flow up to the top.
  3. WSUS maintenance can be performed simultaneously on multiple servers in the same tier. When doing so, ensure that one tier is done before moving onto the next one. The cleanup and re-index steps described below should be run on all WSUS servers, regardless of whether they are a replica WSUS server or not (see this section for info related to determining if a WSUS server is a replica).
  4. Ensure that SUPs do not sync during the maintenance process, as this may cause a loss of some work already done. Check the SUP sync schedule and temporarily set it to manual during this process.

    Select the option
  1. Note that if you have multiple SUPs off the primary site or CAS which do not share the SUSDB, consider the WSUS server that syncs with the first SUP on the site as residing in a tier below the site. For example, my CAS site has two 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.

    The server named “2012” syncs with “New” and it would be considered in the second tier.

How to perform WSUS maintenance


1. Backup the WSUS database

Backup the WSUS database (SUSDB) using the desired method. See the article Create a Full Database Backup (SQL Server) for related information.

2. Create custom indexes

The following steps should be used to create custom indexes in the SUSDB database. This is a one-time process, which is optional but recommended, as doing so will greatly improve performance during subsequent cleanup operations.

  1. Ensure you have a backup of the SUSDB database.
  2. Use SQL Management Studio to connect to the SUSDB database, in the same manner as described in the Re-index the WSUS database section below.
  3. Run the following script against SUSDB, to create two custom indexes:
    -- Create custom index in tbLocalizedPropertyForRevisionUSE [SUSDB]CREATE NONCLUSTERED INDEX [nclLocalizedPropertyID] ON [dbo].[tbLocalizedPropertyForRevision](     [LocalizedPropertyID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]-- Create custom index in tbRevisionSupersedesUpdateCREATE NONCLUSTERED INDEX [nclSupercededUpdateID] ON [dbo].[tbRevisionSupersedesUpdate] (      [SupersededUpdateID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

     

    NOTE If custom indexes have been previously created, running the script again will result in an error similar to the following:
    Msg 1913, Level 16, State 1, Line 4The operation failed because an index or statistics with name 'nclLocalizedPropertyID' already exists on table 'dbo.tbLocalizedPropertyForRevision'.

Back to top

3. Re-index the WSUS database

Re-index the WSUS database (SUSDB) using the SQL script from the Re-index the WSUS 3.0 Database article.

The steps to connect to SUSDB and perform the re-index differ, depending on whether SUSDB is running in SQL Server or Windows Internal Database (WID). To determine where SUSDB is running, check the registry key on the WSUS server located at HKLM\Software\Microsoft\Update Services\Server\Setup, and the SQLServerName value.

If the value contains just the server name or server\instance, SUSDB is running on a SQL Server. If the value includes the string ##SSEE or ##WID in it, SUSDB is running in Windows Internal Database, as shown:

SqlServerName-SSEE

SqlServerName-WID

If SUSDB was installed on Windows Internal Database (WID)
If SUSDB was installed on WID, SQL Management Studio Express must be installed locally in order to run the re-index script. Here’s an easy way to determine which version of SQL Server Management Studio Express to install:

After installing SQL Management Studio Express, launch it, and enter the server name to connect to:

  • If the OS is Windows Server 2012 or newer, use \\.\pipe\MICROSOFT##WID\tsql\query
  • If the OS is older than Windows Server 2012, enter \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

 

If SUSDB was installed on SQL Server
If SUSDB was 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, a Query executed successfully message will be displayed in the status bar, and the Results pane will contains messages related to what indexes were rebuilt.

Execute the SQL statement.

How the log should look if the query executed successfully.

Back to top

4. Decline superseded updates

Decline superseded updates in the WSUS server to help clients scan more efficiently. Before declining updates, ensure that the superseding updates are deployed, and that superseded ones are no longer needed. Configuration Manager includes a separate cleanup, which allows it to expire superseded updates based on specified criteria. See the following articles for additional information:


The following SQL query can be run against the SUSDB database, to quickly determine the number of superseded updates. If the number of superseded updates is high (i.e., greater than ~1500), this can cause various software update related issues, on both the server and client sides.

-- Find the number of superseded updatesSelect COUNT(UpdateID) from vwMinimalUpdate where IsSuperseded=1 and Declined=0

Superseded updates can be manually declined via the WSUS console, or you can run this PowerShell script (right click and choose “Save target as…”). Simply download the script, remove the .txt file extension, and ensure that is it saved 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.

If Configuration Manager is set to immediately expire superseded updates (see below), the PowerShell script can be used to decline all superseded updates. 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 replicas, such as secondary site SUPs. Check the settings shown in the second screenshot below to determine whether a WSUS server is a replica,

Check the option

 

Check the option

If updates are not configured to be immediately expired in Configuration Manager, the PowerShell script must be run with an exclusion period that matches the Configuration Manager setting for number of days to expire superseded updates. In this case, it would be 60 days since SUP component properties are configured to wait two months before expiring superseded updates:

Select the option

The following command lines illustrate the various ways that the PS script can be run (if the script is being run on the WSUS server, LOCALHOST can be used in place of the actual SERVERNAME):

Decline-SupersededUpdatesWithExclusionPeriod.ps1 -UpdateServer SERVERNAME -Port 8530 –SkipDeclineDecline-SupersededUpdatesWithExclusionPeriod.ps1 -UpdateServer SERVERNAME -Port 8530 –ExclusionPeriod 60Decline-SupersededUpdatesWithExclusionPeriod.ps1 -UpdateServer SERVERNAME -Port 8530Decline-SupersededUpdatesWithExclusionPeriod.ps1 -UpdateServer SERVERNAME -UseSSL -Port 8531

Running the script with a –SkipDecline and –ExclusionPeriod 60 to gather information about updates on the WSUS server, and how many updates could be declined:

Windows PowerShell window running SkipDecline and ExclusionPeriod 60.

Running the script with –ExclusionPeriod 60, to decline superseded updates older than 60 days:

Windows PowerShell with just ExclusionPeriod 60 running.

Output and progress indicator is displayed while the script is running. Note the SupersededUpdates.csv file, which will contain a list of all updates which are declined by the script:

Windows PowerShell outputand progress indicator.

Once superseded updates have been declined, for best performance, SUSDB should be re-indexed again. See the Re-index the WSUS database section above for related information.

Back to top

5. Run the WSUS Server Cleanup Wizard

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. If timeouts continue to occur, see the SQL alternative in the section “HELP! My WSUS has been running for years without ever having maintenance done and the cleanup wizard keeps timing out” below. It may take multiple hours or days for the Server Cleanup Wizard or SQL alternative to run through completion.

The WSUS Server Cleanup Wizard is run from the WSUS console. It is located under Options as shown here:

The WSUS Server Cleanup Wizard.

See the TechNet article Use the Server Cleanup Wizard for more information.

The WSUS Server Cleanup Wizard.

The cleanup is finished once it 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 that you will need to start it again, or use the SQL alternative.

The WSUS Server Cleanup Wizard when finished.

Once superseded updates have been declined, for best performance, SUSDB should be re-indexed again. See the Re-index the WSUS database section above for related information.

Back to top

6. Troubleshooting

HELP! My WSUS has been running for years without ever having maintenance done and the cleanup wizard keeps timing out!

There are two different options here:

  1. Reinstall WSUS with a fresh database. There are a number of caveats related to this, including length of initial sync, and full client scans against SUSDB, versus differential scans.
  2. Ensure you have a backup of the SUSDB database, then run a re-index. When that completes, run the following 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	FOR	SELECT Col1 FROM #results	OPEN WC	FETCH NEXT FROM WC	INTO @var1	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		CLOSE WC	DEALLOCATE WC		DROP TABLE #results

Running the Decline-SupersededUpdatesWithExclusionPeriod.ps1 script times out when connecting to the WSUS server, or a 401 error occurs while running

If errors occur when attempting to use the PS script to decline superseded updates, an alternative SQL script can be run against SUDB.

  1. If Configuration Manager is being used with WSUS, check the Software Update Point Component Properties and Supersedence Rules to see how superseded updates are expired (i.e., immediately, or after X months; make note of this).

    WSUS Approve Updates screen.
  2. Backup the SUSDB database before proceeding further, if this has not already been done.
  3. Use SQL Management Studio to connect to SUSDB.
  4. Run the following query. Note that the number 90 in the line that includes "DECLARE @thresholdDays INT = 90" should correspond with the Supersedence Rules from #1 above, and the correct number of days that aligns with the number of months configured in Supersedence Rules. If this is set to expire immediately, the value in the SQL query for @thresholdDays should be set to zero.
    -- Decline superseded updates in SUSDB; alternative to Decline-SupersededUpdatesWithExclusionPeriod.ps1DECLARE @thresholdDays INT = 90 -- Specify the number of days between today and the release date for which the superseded updates must not be declined (i.e., updates older than 90 days). This should match configuration of supersedence rules in SUP component properties, if ConfigMgr is being used with WSUS.DECLARE @testRun BIT = 0 -- Set this to 1 to test without declining anything.-- There shouldn't be any need to modify anything after this line.DECLARE @uid UNIQUEIDENTIFIERDECLARE @title NVARCHAR(500)DECLARE @date DATETIMEDECLARE @userName NVARCHAR(100) = SYSTEM_USERDECLARE @count INT = 0DECLARE DU CURSOR FOR	SELECT MU.UpdateID, U.DefaultTitle, U.CreationDate FROM vwMinimalUpdate MU	JOIN PUBLIC_VIEWS.vUpdate U ON MU.UpdateID = U.UpdateIdWHERE MU.IsSuperseded = 1 AND MU.Declined = 0 AND MU.IsLatestRevision = 1	AND MU.CreationDate < DATEADD(dd,-@thresholdDays,GETDATE())ORDER BY MU.CreationDatePRINT 'Declining superseded updates older than ' + CONVERT(NVARCHAR(5), @thresholdDays) + ' days.' + CHAR(10)OPEN DUFETCH NEXT FROM DU INTO @uid, @title, @dateWHILE (@@FETCH_STATUS > - 1)BEGIN	SET @count = @count + 1	PRINT 'Declining update ' + CONVERT(NVARCHAR(50), @uid) + ' (Creation Date ' + CONVERT(NVARCHAR(50), @date) + ') - ' + @title + ' ...'	IF @testRun = 0		EXEC spDeclineUpdate @updateID = @uid, @adminName = @userName, @failIfReplica = 1	FETCH NEXT FROM DU INTO @uid, @title, @dateENDCLOSE DUDEALLOCATE DUPRINT CHAR(10) + 'Attempted to decline ' + CONVERT(NVARCHAR(10), @count) + ' updates.'
  5. To check progress, monitor the Messages tab in the Results pane.


What if I find out I needed one of the updates that I declined?
If you decide you need one of these declined updates in Configuration Manager, you can get it back in WSUS by right clicking on the update, and selecting Approve. Change the approval to Not Approved, and then resync the SUP to bring the update back in.

WSUS Approve Updates screen.

If the update is no longer in WSUS, it can be imported from the Microsoft Update Catalog, provided it has not been expired/removed from the catalog.

How to import updates in WSUS.

Back to top

7. Automating WSUS maintenance

We’re often asked whether 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 perform maintenance on them first, and 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 a maintenance task though SQL Server Management Studio Express. That said, in cases where WID is used you can use the Task Scheduler with SQLCMD mentioned earlier. If you go this route, it is 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. I schedule this overnight before my AM sync, so I have time to check on it before my sync runs.

Needed/helpful links:


Setting up the WSUS Cleanup Task in Task Scheduler
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.

    WSUS Create a Task screen.
  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 okay, and can be ignored.

    WSUS add a line of script to start the task.

  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.

    Set the WSUS Edit Trigger for the task.

  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-SupersededUpdatesWithExclusionPeriod.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:

    WSUS Create Basic Task Wizard screen.

  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:

    Set the frequency for that task in the Create Basic Task Wizard.

  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
    How the script should look in the Create Basic Task Wizard.

     
  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:

    Task Scheduler confirmation popup window.

  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

  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.

    Type a name for your WSUS maintenance plan.
  2. Click on subplan1 and then ensure your Toolbox is in context:

    Ensure your Toolbox is in context.
  3. Drag and drop the task Execute T-SQL Statement Task:

    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:

    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.

    WSUS New Job Schedule screen.
  6. While creating the maintenance plan, consider adding a backup of the SUSDB into the plan as well. I usually backup first, then re-index. Note, this may add additional time to the schedule.


Putting it all together
When running this in a hierarchy, the WSUS cleanup run should be done from the bottom of the hierarchy up, but you using the script to decline superseded updates, it is done run from the top down.

Since a sync can’t be done during the actual cleanup, it is suggested to schedule/complete all tasks overnight, then check on their completion via the logging the following morning, before the next scheduled sync. If something failed, maintenance can be rescheduled for the next night, once the underlying issue is identified and resolved.

These tasks may run faster or slower depending on the environment, and timing of the 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:

Time   Tasks
12:00 AM Tier1-Decline  
12:15 AM Tier2-Decline  
12:30 AM Tier3-Decline  
1:00 AM Tier3 WSUS Cleanup  
2:00 AM Tier3 Re-index Tier2 WSUS Cleanup
3:00 AM Tier1-Cleanup Tier2 Re-index
4:00 AM Tier1 Re-index  


For more information regarding SUP maintenance in Microsoft Configuration Manager, see the following articles:

Special thanks to Vinay Pamnani for providing the script to decline superseded updates with an exclusion period and to The Scripting Guy

Meghan Stewart | Microsoft Support Escalation Engineer 
Eric Ellis | Microsoft Senior Support Escalation Engineer 

Back to top