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]
TL;DR
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
Great Article! I usually use this procedure to maintain the health of WSUS with SUP.
https://blogs.technet.microsoft.com/configurationmgr/2016/01/26/the-complete-guide-to-microsoft-wsus-and-configuration-manager-sup-maintenance/
I think Kannan already linked the blog post you mentioned in the Resources section of the post 🙂 Anyways glad to know you liked the article !
Thanks Douglas!
Hi thanks for the great post – i have one question.
The 3. thing “Cleanup Obsolete Updates” i am running it now, and it shows:
Deleting 855493 Remaining rows:17800
Deleting 855416 Remaining rows:17799
Deleting 855415 Remaining rows:17798
Now after running approx 1,5 hour… I have timed it, and it seems to delete one row every 50-60 second, so my calculations tell me that the script will run somthing like 296 hours to complete ?? 12 days it looks like… Will et start over, if i terminate the script, or will my database be damaged ? can i stop the script, and sync my WSUS and then start the script again, or will i have to run everything now, and wait with wsus sync until eerything has finished ?
Yours
Erling B. Kjeldsen
How is your performance of the SQL box? How about the server performance? Is it ok, I don’t think a row deletion should take that long. I will let Kannan to answer your questions with details.
Hi Anoop –
I have checked, and the SQL box is only using 20% cpu, but after i have changed so that SQL can use more of the RAM (16gb) now its doing 2 deleted row pr. minute approx.. so it seems that i am down to approx 5-6 days and not 12 as first calculated – but i still might need to stop the script and do a wsus sync sometime during the next 5-6 days.
Latest progress is now that the script has been running for 3 hours and 6 minutes and we still have above 17600 rows to delete:
Deleting 846829 Remaining rows:17661
Deleting 846827 Remaining rows:17660
Deleting 846719 Remaining rows:17659
Deleting 846689 Remaining rows:17658
I don’t think if you stop in between that will damage your SQL DB
Thanks – i will keep it running for a day or two – and se how far it gets..
Hope that this will not be for nothing, when i have synced updates in a couple of days and starts the script again.. 🙂
Maybe Kannan or you can tip me whether i must run all the steps from the top again after a sync, or just run this script again at that time ?
It’s recommended to follow all the steps. Not only the script. I will let Kannan to confirm
okay thanks – i will start from the beginning if i have to interrupt the running script and do a sync.
Thanks Anoop,
Hi Erling,
Based on your updates, it will take time to clean up the Obsolete update. It is based on your product selection configured in SUP. I would recommend to re-think the product/classification which is needed, rest you could unselect the same. My environment, it will take 1 day to clean up nearly 1800 updates.
Yes, you need to perform all the steps to have better performance for SUS DB.
Good article Anoop!
I’m not much of a SQL person so others may have noticed and corrected this on their end already. The first step shows to executed “spgetobsoleteupdatescleanup” on the SUSDB. That command fails so just in case, I zoomed in on the screenshot which as the word “TO” in the command. So I believe it should be “spgetobsoleteupdatesTOcleanup”.
I’m on to the next steps in the guide 🙂
Hi Chris,
I have updated the syntax of the SQL statement. Thanks for being pointout.