This post will help you to fix SCCM WSUS maintenance. This post will help the admins who manage the SCCM infrastructure. There are several other blogs about the same topic, but I’m sharing my implementation experience with this post. I have been inspired by the blogs mentioned in the references section of this post.
[Related Post – How to Setup WSUS Cleanup Task from SCCM console & The complete guide to Microsoft WSUS and Configuration Manager SUP maintenance]
Table of Contents
Why Perform WSUS DB Maintenance or Cleanup
The following are the high-level activities of Software Update clients which lead to scan timeout error. To fix scan timeout errors for your workplace environment, you have to perform WSUS DB maintenance or cleanup regularly.
- Whenever the SUP sync with Microsoft, the metadata information is updated in the catalog file.
- The catalog file size also increased every time post the SUP sync with Microsoft.
- The catalog file has all update (Active, Superseded, Expired) patch information based on Microsoft product selection in the environment.
- Due to this above steps, most of the time clients fail to complete the scan due to timeout errors or unable to load the catalog file.
For better performance for SUS DB with active patch information, need to perform WSUS maintenance activity on a monthly interval. SCCM WSUS Maintenance cleanup tasks will help you to understand better.
Better Understand SCCM Icons – Software Update
| Icon | Patch Status | Description |
 | Active | These Green icons indicate ACTIVE/ available updates ready for deployment |
 | Expired | The black X icons represent expired SCCM software updates. You can also identify expired software updates by viewing the Expired column for the software update when it displays in the SCCM console. |
 | Superseded | The yellow star icon represents a superseded SCCM software update. You can also identify superseded software updates by viewing the Superseded column for the software update when it displays in the SCCM console. |
 | Invalid | The icon with the red X represents an invalid software update. Invalid SCCM software updates are in active deployment, but for some reason, the content (software update files) is not available. |
 | Metadata | Blue icons mean Metadata-only SCCM software updates are available in the SCCM console for reporting. You cannot deploy or download metadata-only software updates because a software update file is not associated with the software updates metadata. |
Steps Involved in SCCM WSUS Maintenance Tasks
There are three (3) steps involved in these SCCM WSUS Maintenance tasks. These three tasks are explained in the below sections of the post with more details.
- Re-index the WSUS DB
- Cleanup Obsolete Updates
- Decline superseded updates
Important:
- Do not sync your SUPs during this SCCM WSUS Maintenance process as it is possible you will lose some of the work you have already done if you do.
- Deselect the non-patched Product and Classification in Software Update Point (SUP) configuration.
[Related Post – How to Setup WSUS Cleanup Task from SCCM console]
How to Identify & Connect the WSUS DB
When you have SCCM secondary servers or remote SUP servers, you might have installed WSUS DB on Windows Internal Database. I don’t recommend to use Windows Internal Database and SQL express version to install WSUS DB. WSUS DB can be installed either on SQL Server or Windows Internal Database (WID).
For WSUS DB installed on SQL Server:
If WSUS DB is installed on SQL, you connect the WSUS DB using SQL management studio.
For WSUS DB Installed on Windows Internal Database (WID):
- Download and install SQL Management Studio in the below link https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017
- Connect the SUS DB with below options,
- 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: Always run the SQL Management studio with administrator privilege mode, else query will return the execution error.
Before running the SCCM WSUS maintenance task, follow the task execution in the following order mentioned in the below table. More details about SQL 1, SQL 2, SQL 3, and SQL 4 tasks are explained below.
| Software Update Point | Sync Source | To find Number of Obsolete Updates | Re-index job (Bottom to Top) | Cleanup Obsolete Updates (Bottom to Top) | Decline Superseded Updates (Top to Bottom) |
| Server 1 | Microsoft | SQL 1 | SQL 2 ( once the SQL 3 job completed for downstream servers) | SQL 2 ( SQL 2 re-index job is completed) | SQL 4 (1st Run) |
| Server 2 | Server 1 | SQL 1 | SQL 2 ( 1st run concurrently) | SQL 3 ( 1st run concurrently) | SQL 4 (2nd Run or currently run post upstream server job completion) |
| Server 3 | Server 1 | SQL 1 | SQL 2 ( 2nd run or concurrently after 1st run) | SQL 3 ( 2nd run or concurrently after 1st run) | SQL 4 (2nd Run or currently run post upstream server job completion) |
Find the Number of Obsolete Updates from SQL Management Studio (SQL 1)
To find the number of obsolete updates present in the SCCM managed WSUS database execute the below query. This step is the first step toward the cleanup of SCCM WSUS Maintenance. The result will be displayed in the bottom side right corner (Marked in RED).
SQL query : spgetobsoleteupdatestocleanup
How to Re-index the SCCM WSUS DB (SQL 2)
To re-index the SUS DB, copy and paste the query in the new query window and then click Execute. This step is the second step toward the cleanup of SCCM WSUS Maintenance. Once the Query Executed successfully considered that WSUS DB successfully re-indexed.
Download the Re-index query from the following Microsoft TechNet gallery.
https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61
How to Cleanup Obsolete Updates from SCCM (SQL 3)
You can use stored procedure query (given below) in the SQL Management studio and then Execute. This step is the third step toward the cleanup of SCCM WSUS Maintenance.
Execute the stored procedure query in the SQL Management studio. Make sure you have the backup of the SQL DB.
[Related Post – How to Setup WSUS Cleanup Task from SCCM console]
DECLARE @var1 INT DECLARE @msg nvarchar(100) DECLARE @count BIGINT CREATE TABLE #results (Col1 INT) INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup Select @count=count(Col1) From #results 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) + ' Remaining rows:' + convert (varchar(10),@count) RAISERROR(@msg,0,1) WITH NOWAIT EXEC spDeleteUpdate @[email protected] Set @[email protected] -1 FETCH NEXT FROM WC INTO @var1 END CLOSE WC DEALLOCATE WC DROP TABLE #results
It will delete any obsolete or OLD update from the SUS DB. It will show deletion of rows in previously shown in SQL 1 query statement.
Decline Superseded Updates from SQL Management Studio (SQL 4)
Additionally, you may want to decline superseded updates in the WSUS server, so it helps your clients scan more efficiently. This step is the fourth step toward the cleanup of SCCM WSUS Maintenance.
Note: The above SQL 4 is not applicable for ‘Replica’ WSUS Servers..
- Download the PowerShell script from this MS URL https://msdnshared.blob.core.windows.net/media/TNBlogsFS/prod.evol.blogs.technet.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/00/69/06/Decline-SupersededUpdatesWithExclusionPeriod.ps1.txt
- Open the PowerShell with administrator privilege
- In PowerShell, navigate the downloaded folder that contains the script
- Run the command as > .\SupersededUpdates.ps1 -UpdateServer -Port -SkipDecline
5. Once done, it will give you a list of updates that are superseded.
6. After that run another command as > .\SupersededUpdates.ps1 -UpdateServer -Port
7. It will decline the superseded updates on your Upstream server
8. Repeat the same steps post all secondary site task is completed.
Resources
- The complete guide to Microsoft WSUS and Configuration Manager SUP maintenance
- Microsoft blog post SCCM WSUS Maintenance – here
- Fully Automate Software Update Maintenance in SCCM
