If you run a Windows Server Update Services (WSUS) server, you know that sometimes it can develop problems. Specifically the WSUS database can need a manual clean up. There are several scripts around that handle this but I found one HERE that helped me quite a bit.

The WSUS Server Cleanup utility: Removing unused and obsolete updates from the database helps keeping the overall size of the catalog down to a minimum. New clients will have to download the catalog (and also clients falling over from one Software Update Point to another), and size matters when dealing with slow WAN links and thousands of. May 14, 2014 Update history shows several updates installed today, but 'Security Update for IE 11 for Windows 8.1 for x64 based systems (KB2961851) without KB2919355' failed. What do I do?!I am currently using FireFox and have changed my update preferences to non-automatic. D data definition language. Data dictionary. Metadata that keeps track of database objects such as tables, indexes, and table columns.For the MySQL data dictionary, introduced in MySQL 8.0, metadata is physically located in InnoDB file-per-table tablespace files in the mysql database directory. For the InnoDB data dictionary, metadata is physically located in the InnoDB system.

Make sure you meet the system requirements. Chrome doesn’t work with the following older platforms: Windows XP; Windows Vista; Mac OS X 10.6, 10.7, and 10.8. Update troubleshooter report. How do I do it?? Attempt number 8 to update from 14931 to 14936. Another wasted 2 hours. It asks if I want to try their fix, but nothing says how, nor is there a link. Amy suggestions? BTW, I think the database may have gotten wiped on a cleanup. Not retrievable.

Note that this script does not replace the WSUS SERVER CLEAN UP WIZARD. If you are having problems with that (like SERVER NODE RESET messages), click HERE for the fix.

To run this script:

1. Download it directly from us HERE and unzip the contents
   .
2. Start a PowerShell as an Admin
   .
3. If you are running WSUS from a Windows Internal Database on Server 2012 enter the following command:
   .
   sqlcmd -I -S .pipeMICROSOFT##WIDtsqlquery -i C:WsusDBMaintenance.sql
   .
   If you are running WSUS from a from a Windows Internal Database on Server 2008 or SBS 2011, enter the following command:
   .
   sqlcmd.exe -I -S np:.pipeMSSQL$MICROSOFT##SSEEsqlquery -i C:WSUSDBMaintenance.sql
   .updated Jul 18 2018
   (Be sure adjust the path of C:WsusDBMaintenance.sql to reflect where you actually put the script)
   .
4. Wait.

I have not had this take more than 15 minutes but I have read reports of it taking a VERY long time.

I found THIS post and THIS post helpful when dealing with the WSUS problem.

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 in this article.

Original product version: Windows Servers, Windows Server Update Services, Configuration Manager
Original KB number: 4490644

Introduction

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's not uncommon for conscientious Configuration Manager administrators to be unaware that WSUS maintenance should be run at all. Most of us just set up WSUS servers because it's a prerequisite for a software update point (SUP). Once the SUP is set up, 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, 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 easy and doesn't take long for WSUS servers that have been well maintained from the start. However, if it has been some time since WSUS maintenance was done, the cleanup may be more difficult or time consuming the first time. It will be much easier or faster in subsequent months.

Maintain WSUS while supporting Configuration Manager current branch version 1906 and later versions

If you are using Configuration Manager current branch version 1906 or later versions, enabling the WSUS Maintenance options in the software update point configuration at the top-level site is recommended to automate the cleanup procedures after each synchronization. This would effectively handle all cleanup operations described in this article except backup and reindexing of WSUS database. You should still automate backup of WSUS database along with reindexing of the WSUS database on a schedule.

For more information about software update maintenance in Configuration Manager, see Software updates maintenance.

Important considerations

Note

If you are utilizing the maintenance features that have been added in Configuration Manager, version 1906, you don't need to consider these items since Configuration Manager handles the cleanup after each synchronization.

1. Before you start the maintenance process, read all of the information and instructions in this article.

2. When using WSUS along with downstream servers, WSUS servers are added from the top down, but should be removed from the bottom up. When syncing or 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, you should start at the bottom of the hierarchy.

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 reindex steps described below should be run on all WSUS servers, regardless of whether they are a replica WSUS server or not (see Decline superseded updates for information related to determining if a WSUS server is a replica).

4. Ensure that SUPs don't 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.

5. If you have multiple SUPs of the primary site or central administration sit (CAS) which don't 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.

Perform WSUS maintenance

The basic steps necessary for proper WSUS maintenance include the following:

Back up the WSUS database

Back up the WSUS database (SUSDB) using the desired method. For related information, see Create a Full Database Backup.

Create custom indexes

This process is optional but recommended, as creating custom indexes greatly improves performance during subsequent cleanup operations.

If you are using Configuration Manager current branch version 1906 or a later version, it is recommended that you use Configuration Manager to create the indexes by configuring the Add non-clustered indexes to the WSUS database option in the software update point configuration for the top-most site.

If you are using an older version of Configuration Manager or standalone WSUS servers, follow these steps to create custom indexes in the SUSDB database. for each SUSDB, this is a one-time process.

1. Make sure that 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 Reindex the WSUS database section.

3. Run the following script against SUSDB, to create two custom indexes:

   If custom indexes have been previously created, running the script again results in an error similar to the following:

   Msg 1913, Level 16, State 1, Line 4
   The operation failed because an index or statistics with name 'nclLocalizedPropertyID' already exists on table 'dbo.tbLocalizedPropertyForRevision'.

Reindex the WSUS database

To reindex the WSUS database (SUSDB), use the Re-index the WSUS Database T-SQL script.

The steps to connect to SUSDB and perform the reindex differ, depending on whether SUSDB is running in SQL Server or Windows Internal Database (WID). To determine where SUSDB is running, check value of the SQLServerName registry entry on the WSUS server located at the HKEY_LOCAL_MACHINESoftwareMicrosoftUpdate ServicesServerSetup subkey.

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

If SUSDB was installed on WID

If SUSDB was installed on WID, SQL Server Management Studio Express must be installed locally to run the reindex script. Here's an easy way to determine which version of SQL Server Management Studio Express to install:

• For Windows Server 2012 or later versions:

  • Go to C:WindowsWIDLog and find the error log that contains the version number.

  • Look up the version number in How to determine the version, edition and update level of SQL Server and its components. This value tells you what Service Pack (SP) level that WID is running. Include the SP level when searching the Microsoft Download Center for SQL Server Management Studio Express, because it does sometimes matter.

• For Windows Server 2008 R2 or previous versions:

  • Go to C:WindowsSYSMSISSEEMSSQL.2005MSSQLLOG and open up the last error log with Notepad. At the top, there will be a version number (for example 9.00.4035.00 x64). Look up the version number in How to determine the version, edition and update level of SQL Server and its components. This will tell you what Service Pack level it is running. Include the SP level when searching the Microsoft Download Center for SQL Server Management Studio Express.

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

• If the OS is Windows Server 2012 or later versions, use .pipeMICROSOFT##WIDtsqlquery.
• If the OS is older than Windows Server 2012, enter .pipeMSSQL$MICROSOFT##SSEEsqlquery.

For WID, if errors similar to the following occur when attempting to connect to SUSDB using SQL Server Management Studio (SSMS), try launching SSMS using the Run as administrator option.

If SUSDB was installed on SQL Server

If SUSDB was installed on full SQL Server, launch SQL Server Management Studio and enter the name of the server (and instance if needed) when prompted.

Tip

Alternatively, a utility called sqlcmd can be used to run the reindex script. For more information, see Reindex the WSUS Database.

Running the script

To run the script in either SQL Server Management Studio or SQL Server Management Studio Express, select New Query, paste the script in the window and then select Execute. When it is finished, a Query executed successfully message will be displayed in the status bar, and the Results pane will contain messages related to what indexes were rebuilt.

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 (that is, greater than ~1500), this can cause various software update related issues, on both the server and client sides.

If you are using Configuration Manager current branch version 1906 or a later version, it's recommended that you automatically decline the superseded updates by enabling the Decline expired updates in WSUS according to supersedence rules option in the software update point configuration for the top-most site.

When you use this option, you can see how many updates were declined by reviewing the WsyncMgr.log file after the synchronization process finishes. If you use this option, you do not need to use the script described later in this section (either by manually running it or by setting up as task to run it on a schedule).

If you are using standalone WSUS servers or an older version of configuration Manager, you can manually decline superseded updates by using the WSUS console, or you can run this PowerShell script (To download the script, right-click the link and select Save target as...). Download the script, remove the .txt file extension, and save the file with a .PS1 extension.

Note

This script is provided as is and it should be fully tested in a lab before being used in production. Microsoft makes no guarantees regarding the use of this script in any way. Always run the script with the -SkipDecline parameter first, to get a summary of how many superseded updates will be declined.

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.

You don't need to run the PowerShell script on WSUS servers that are set as replicas, such as secondary site SUPs. To determine whether a WSUS server is a replica, check the Update Source settings.

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:

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

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:

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

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

Note

If issues occur when attempting to use the above PowerShell script to decline superseded updates, see the section Running the Decline-SupersededUpdatesWithExclusionPeriod.ps1 script times out when connecting to the WSUS server, or a 401 error occurs while running for troubleshooting steps.

After superseded updates have been declined, for best performance, SUSDB should be reindexed again. For related information, see Reindex the WSUS database.

Run the WSUS Server Cleanup Wizard

WSUS Server Cleanup Wizard provides options to clean up the following items:

• Unused updates and update revisions (also known as Obsolete updates)
• Computers not contacting the server
• Unneeded update files
• Expired updates
• Superseded updates

In a Configuration Manager environment, Computers not contacting the server and Unneeded update files options are not relevant because Configuration Manager manages software update content and devices, unless either the Create all WSUS reporting events or Create only WSUS status reporting events options are selected under Software Update Sync Settings. If you have one of these options configured, you should consider automating the WSUS Server Cleanup to perform cleanup of these two options.

If you are using Configuration Manager current branch version 1906 or a later version, enabling the Decline expired updates in WSUS according to supersedence rules option handles declining of Expired updates and Superseded updates based on the supersedence rules that are specified in Configuration Manager. Enabling the Remove obsolete updates from the WSUS database option in Configuration Manager current branch version 1906 handles the cleanup of Unused updates and update revisions (Obsolete updates). It's recommended to enable these options in the software update point configuration on the top-level site to allow Configuration Manager to clean up the WSUS database.

If you've never cleaned up obsolete updates from WSUS database before, this task may time out. You can review WsyncMgr.log for more information, and manually run the SQL script that is specified in HELP! My WSUS has been running for years without ever having maintenance done and the cleanup wizard keeps timing out once, which would allow subsequent attempts from Configuration Manager to run successfully. For more information about WSUS cleanup and maintenance in Configuration Manager, see the docs.

For standalone WSUS servers, or if you are using an older version of Configuration Manager, it is recommended that you run the WSUS Cleanup wizard periodically. 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, reindex with step 2 and step 3 first, then run the cleanup with only the Unused updates and update revisions option checked.

If you have never run WSUS Cleanup wizard, running the cleanup with Unused updates and update revisions may require a few passes. If it times out, run it again until it completes, and 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 Server alternative in HELP! My WSUS has been running for years without ever having maintenance done and the cleanup wizard keeps timing out. It may take multiple hours or days for the Server Cleanup Wizard or SQL alternative to run through completion.

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

For more information, see Use the Server Cleanup Wizard.

After it reports the number of items it has removed, the cleanup finishes. If you do not see this information returned on your WSUS server, it is safe to assume that the cleanup timed out. In that case, you will need to start it again or use the SQL alternative.

After superseded updates have been declined, for best performance, SUSDB should be reindexed again. See the Reindex the WSUS database section for related information.

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 reindex. When that completes, run the following script in SQL Server Management Studio or SQL Server Management Studio Express. After it finishes, follow all of the above instructions for running maintenance. This last step is necessary because the spDeleteUpdate stored procedure only removes unused updates and update revisions.

Note

Before you run the script, follow the steps in The spDeleteUpdate stored procedure runs slowly to improve the performance of the execution of spDeleteUpdate.

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 you attempt 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 along with WSUS, check Software Update Point Component Properties > Supersedence Rules to see how quickly superseded updates expire (such as immediately or after X months). Make a note of this setting.

2. If you have not yet backed up the SUSDB database, do so before proceeding further.

3. Use SQL Server Management Studio to connect to SUSDB.

4. Run the following query. The number 90 in the line that includes DECLARE @thresholdDays INT = 90 should correspond with the Supersedence Rules from step 1 of this procedure, and the correct number of days that aligns with the number of months that is configured in Supersedence Rules. If this is set to expire immediately, the value in the SQL query for @thresholdDays should be set to zero.

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 the update, and selecting Approve. Change the approval to Not Approved, and then resync the SUP to bring the update back in.

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

Automating WSUS maintenance

Note

If you are using Configuration Manager version1906 or a later version, automate the cleanup procedures by enabling the WSUS Maintenance options in the software update point configuration of the top-level site. These options handle all cleanup operations that are performed by the WSUS Server Cleanup Wizard. However, you should still automatically back up and reindex the WSUS database on a schedule.

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 reindex 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's important that you don't sync your WSUS servers/SUPs during this maintenance period! If you do, it's 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.

Data Bases Obsolete But Update Fails At 78%25

Needed/helpful links:

Setting up the WSUS Cleanup task in Task Scheduler

Note

As mentioned previously, if you are using Configuration Manager current branch version 1906 or a later version, automate the cleanup procedures by enabling the WSUS Maintenance options in the software update point configuration of the top-level site. For standalone WSUS servers or older versions of Configuration Manager, you can continue to use the following steps.

Databases Obsolete But Update Fails At 78% 11

The Weekend Scripter blog post mentioned in the previous section contains basic directions and troubleshooting for this step. However, I'll walk you through the process in the following steps.

1. Open Task Scheduler and select Create a Task. On 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, and 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 WSUS Cleanup script found here. This script performs cleanup options that Configuration Manager current branch version 1906 doesn't do, but you can uncomment them if you are using standalone WSUS or an older version of Configuration Manager. If you would like a log, you can modify the last line of the script as follows:

   You will get an FYI/warning in Task Scheduler when you save, but this is okay, and can be ignored.

3. On the Triggers tab, set your schedule for once a month or on any schedule you want. Again, you must ensure that you don't sync your WSUS during the entire cleanup and reindex time.

4. Set any other conditions or settings you would like to tweak as well. 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 first Sunday every 3 months.

Setting up the SUSDB reindex for WID using SQLCMD and Task Scheduler

1. Save the script here as a .sql file (for example, 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 reindex. This means I would schedule this task for every first 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 FilesMicrosoft SQL Server110ToolsBinnSQLCMD.exe' -S .pipeMicrosoft##WIDtsqlquery -i C:WSUSSUSDBMaint.sql -o c:WSUSreindexout.txt

5. You will get a warning, similar to the one you got when creating the cleanup task. Click Yes to accept the arguments, and 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 doesn't 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 Server to create or manage maintenance plans.

Databases Obsolete But Update Fails At 78% End

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. Select subplan1 and then ensure your Toolbox is in context:

3. Drag and drop the task Execute T-SQL Statement Task:

4. Right-click it and select Edit. Copy and paste the WSUS reindex 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 reindex. This means I would schedule this task to run every first Sunday at 2:00 AM.

6. While creating the maintenance plan, consider adding a backup of the SUSDB into the plan as well. I usually back up first, then reindex. 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. However, when using the script to decline superseded updates, the run should be done from the top down. This is because declining superseded updates is really a type of addition to an update rather than a removal. You're actually adding a type of approval in this case.

Since a sync can't be done during the actual cleanup, it's 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 because my sync isn't scheduled to run.

Not syncing 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 reindexes 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:

Note

Databases Obsolete But Update Fails At 78% Game

If you're using Configuration Manager current branch version 1906 or a later version to perform WSUS Maintenance, Configuration Manager performs the cleanup after synchronization using the top-down approach. In this scenario, you can schedule the WSUS database backup and reindexing jobs to run before the configured sync schedule without worrying about any of the other steps, because Configuration Manager will handle everything else.

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