Fix SCCM Scan Timeout Errors – SCCM WSUS Maintenance

WSUS maintenance Task

11
SCCM WSUS Maintenance Task - Cleanup Tasks

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]

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.

  1. Whenever the SUP sync with Microsoft, the metadata information is updated in the catalog file.
  2. The catalog file size also increased every time post the SUP sync with Microsoft.
  3. The catalog file has all update (Active, Superseded, Expired) patch information based on Microsoft product selection in the environment.
  4. 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

IconPatch StatusDescription
Fix SCCM Scan Timeout Errors - SCCM WSUS Maintenance 1ActiveThese Green icons indicate ACTIVE/ available updates ready for deployment
Fix SCCM Scan Timeout Errors - SCCM WSUS Maintenance 2Expired

 

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.
Fix SCCM Scan Timeout Errors - SCCM WSUS Maintenance 3

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.
Fix SCCM Scan Timeout Errors - SCCM WSUS Maintenance 4InvalidThe 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.
Fix SCCM Scan Timeout Errors - SCCM WSUS Maintenance 5MetadataBlue 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.

  1. Re-index the WSUS DB
  2. Cleanup Obsolete Updates
  3. Decline superseded updates

Important:

  1. 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.
  2. 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):

  1. 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
  2. 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 PointSync SourceTo find Number of Obsolete UpdatesRe-index job (Bottom to Top)Cleanup Obsolete Updates  (Bottom to Top)Decline Superseded Updates (Top to Bottom)
Server 1MicrosoftSQL 1SQL 2 ( once the SQL 3 job completed for downstream servers)SQL 2 ( SQL 2 re-index job is completed)SQL 4 (1st Run)
Server 2Server 1SQL 1SQL 2 ( 1st run concurrently)SQL 3 ( 1st run concurrently)SQL 4 (2nd Run or currently run post upstream server job completion)
Server 3Server 1SQL 1SQL 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 : spgetobsoleteupdatescleanup

Fix SCCM Scan Timeout Errors - SCCM WSUS Maintenance 6

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

Fix SCCM Scan Timeout Errors - SCCM WSUS Maintenance 7

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.

SCCM WSUS Maintenance

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

  1. 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
  2. Open the PowerShell with administrator privilege
  3. In PowerShell, navigate the downloaded folder that contains the script
  4. Run the command as > .\SupersededUpdates.ps1 -UpdateServer -Port -SkipDecline

SCCM WSUS Maintenance

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

SCCM WSUS Maintenance

7. It will decline the superseded updates on your Upstream server

8. Repeat the same steps post all secondary site task is completed.

Resources

11 COMMENTS

  1. 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

  2. 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 ?

    • 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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.