House of Cards–The ConfigMgr Software Update Point and WSUS

A Card house; fun to build but not very solid and when one card falls the whole house often goes down with it. It’s a little like that with the WSUS server and Configuration Manager. Installing WSUS seems so easy but there are still some moving part, and if you get one of the wrong maybe the whole House of Cards falls. Recently I have seen that happen at several customers. This blog post is divided into 3 parts:

1. Introduction

2. Problem overview and symptoms

3. Solutions to fix issues and avoid it in the future

Let’s start by looking at some of the cards in the game:

  • The WSUS IIS App pool: Without this running nothing is really working and clients are unable to perform a successful scan.
  • The WSUS database: You need to ensure that you manage the database and perform some weekly/monthly maintenance like running a custom defragmentation and re-indexing job. Not doing this can result if poor performance and also cause a timeout when trying to run the WSUS Server Cleanup Utility.
  • 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 clients.

What can cause the House of Cards to a fall?

Couple of weeks ago I had three different customers experiencing identical issues almost at the same time causing the House of Cards to a fall. The problem started patch Tuesday, one customer noticed a high utilization on the MPLS line in the main Datacenter caused by the Software Update Point. At almost the same time the WSUS IIS App pool chrased and stopped. Starting the IIS pool up again would once again flood the MPLS line, run for a few minutes and crash again. Other customers said the Windows Update agent didn’t perform a successful scan and reported these errors in the wuahandler.log

OnSearchComplete – Failed to end search job. Error = 0x80244022.
Scan failed with error = 0x8024402

In common for all customers was that the WSUS App pool stopped. When starting the IIS WSUS App pool back, most clients downloaded 5-10 MB. from the software Update Point. Clients looked to be downloading the full catalog from the SUP which was the reason why one customer saw the high utilization of their MPLS lines. 

So why does the WSUS App pool crash and how to fix it?

The WSUS App pool “private memory limit” setting is by default configured to 1,7 GB, the pool  crashed because it couldn’t keep up. In order to fix that, we increased the memory on the Software Update Point and also configure the WSUS App pool memory limit with 8-12 GB. After that we started the App pool and let the clients flood the network for a couple of hours and everything was back to normal. So why couldn’t the WSUS App pool keep up? This is due to the larger number of updates in the catalog which continues to grow over time.  After the Patch Tuesday sync from Microsoft, clients started scanning successfully at first, but the Update catalog reached a size that increased the load on the Software Update Point. Due to the default memory limit of the app pool the server ran out of memory causing the WSUS App pool card to fail and took the House of Cards down with it.

Maintaining the database

Maintaining the susdb database is as important as maintaining the ConfigMgr database meaning that you should run a weekly maintenance task that performs a defragmentation of the database. For this you can run the “Ola Hallengren” defrag solution as described here by Steve Thompson or you can use this script from Microsoft MSDN. Running the script (either of them) for the first time can easily take hours all depending on the number of updates in the database and the server specs.

The WSUS Server Cleanup Utility

Running a WSUS cleanup can be initiated from the WSUS GUI or using PowerShell. Needless to say that PowerShell is the preferred method. Below is an example where I invoke WSUSSeverCleanup to cleanup obsolete updates along with a few other tasks.


Get-WsusServer -Name cm01 -PortNumber 8530

Get-WsusServer | Invoke-WsusServerCleanup –CleanupObsoleteUpdates -CleanupUnneededContentFiles -CompressUpdates -DeclineExpiredUpdates -DeclineSupersededUpdates


Above step will usually run just fine and will tell you how many objects were removed. However if you have had your WSUS server running for a long period and never performed any cleanup before, you will likely run into this time-out error:


Running the WSUS Server Cleanup tool using the GUI will give throw you a connection error like this:



Looking at the underlying error you will see this:

The WSUS administration console was unable to connect to the WSUS Server via the remote API.

Verify that the Update Services service, IIS and SQL are running on the server. If the problem persists, try restarting IIS, SQL, and the Update Services Service.

System.Net.Sockets.SocketException — An existing connection was forcibly closed by the remote host


Stack Trace:
   at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
   at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
   at Microsoft.UpdateServices.UI.SnapIn.Wizards.ServerCleanup.ServerCleanupWizard.OnCleanupComplete(Object sender, PerformCleanupCompletedEventArgs e)

What we are seeing is a SQL stored procedure timeout error. There can several reasons why this happens, database is not indexed, not enough resources etc. A few things you can try to solve this issue is adding more memory, defragment and re-index the database and/or connect to SUSDB in SQL Management Studio and delete the obsolete updates.

I have seen time-out issues so many times at customer who has never performed any past cleanup. Sometimes I can fix the issue by running the database defragmentation job but most often I have to delete the obsolete software update directly in the susdb.


Before deleting any updates first run this stored procure exec spGetObsoleteUpdatesToCleanup. The stored procedure will return the update id of each obsolete update in the database. I have seen any number from 100 to 8,000. What I have learned is that there is no magic limit to when the server will time-out. In order to delete the updates you need to run this store procedure exec spDeleteUpdate @localUpdateID=<50697> where <50697> is the update id. You can choose to delete the updates one by one, or use the script below to delete x-number of updates at the time (thanks for Henrik Rading for helping out). Simply change the number in the SELECT TOP (1000) statement to any number you want to delete. I will warn you, it can easily take several hours to delete the updates and while the process is ongoing you might see WSUS synchronization errors.


IF object_id(‘tempdb..#MyTempTable’) is not null  DROP TABLE #MyTempTable
IF (SELECT CURSOR_STATUS(‘global’,’myCursor’)) >= -1
sp_configure ‘Show Advanced Options’, 1
sp_configure ‘Ad Hoc Distributed Queries’, 1

SELECT TOP (2000) * INTO #MyTempTable
    FROM OPENROWSET(‘SQLNCLI’, ‘Server=(local);Trusted_Connection=yes;’, ‘EXEC susdb.dbo.spGetObsoleteUpdatesToCleanup’)

SELECT LocalUpdateID FROM #MyTempTable

SELECT @Count = COUNT(*) FROM #MyTempTable

SELECT @msg = ‘Number of updates to be deleted:’ +  CAST( @Count AS VARCHAR(10))

OPEN myCursor

    SELECT @msg = ‘Deleting update with ID:’ + CAST (@x AS VARCHAR(10))
    EXEC spDeleteUpdate @localUpdateID=@x
    FETCH NEXT FROM myCursor INTO @x
CLOSE myCursor;
DROP TABLE #MyTempTable;
SELECT @msg = ‘Deletion completed’

How to avoid the House of Cards to Fall

Hopefully by now you have been convinced that it’s required to combine a defragmentation/re-indexing job with running the WSUS Server Cleanup Utility. Personally I run the defragmentation job once a week and after that I run the WSUS Server Cleanup job. The WSUS Server Cleanup process is a PowerShell script from Kaido Järvemets, that will run the WSUS cleanup and mail a nice HTML report with the status of the job. These are the steps you need to perform:

  1. Modify this script with your SMTP information and save the script:
  2. Add-Type -Path "C:\Program Files\Update Services\API\Microsoft.UpdateServices.Administration.dll"

    $UseSSL = $False

    $PortNumber = 8530

    $Server = "cm02"

    $ReportLocation = "E:\Install\WSUS\wsus\WSUS_CleanUpTaskReport.html"

    $SMTPServer = "”

    $SMTPPort = 25

    $To = "Kent Agerlund <>"

    $From = "ConfigMgr <>"

    $WSUSConnection = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer($Server,$UseSSL,$PortNumber)

    #Clean Up Scope

    $CleanupScopeObject = New-Object Microsoft.UpdateServices.Administration.CleanupScope

    $CleanupScopeObject.CleanupObsoleteComputers = $True

    $CleanupScopeObject.CleanupObsoleteUpdates = $True

    $CleanupScopeObject.CleanupUnneededContentFiles = $True

    $CleanupScopeObject.CompressUpdates = $True

    $CleanupScopeObject.DeclineExpiredUpdates = $True

    $CleanupScopeObject.DeclineSupersededUpdates = $True

    $CleanupTASK = $WSUSConnection.GetCleanupManager()

    $Results = $CleanupTASK.PerformCleanup($CleanupScopeObject)

    $DObject = New-Object PSObject

    $DObject | Add-Member -MemberType NoteProperty -Name "SupersededUpdatesDeclined" -Value $Results.SupersededUpdatesDeclined

    $DObject | Add-Member -MemberType NoteProperty -Name "ExpiredUpdatesDeclined" -Value $Results.ExpiredUpdatesDeclined

    $DObject | Add-Member -MemberType NoteProperty -Name "ObsoleteUpdatesDeleted" -Value $Results.ObsoleteUpdatesDeleted

    $DObject | Add-Member -MemberType NoteProperty -Name "UpdatesCompressed" -Value $Results.UpdatesCompressed

    $DObject | Add-Member -MemberType NoteProperty -Name "ObsoleteComputersDeleted" -Value $Results.ObsoleteComputersDeleted

    $DObject | Add-Member -MemberType NoteProperty -Name "DiskSpaceFreed" -Value $Results.DiskSpaceFreed

    #HTML style

    $HeadStyle = "<style>"

    $HeadStyle = $HeadStyle + "BODY{background-color:peachpuff;}"

    $HeadStyle = $HeadStyle + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"

    $HeadStyle = $HeadStyle + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"

    $HeadStyle = $HeadStyle + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:palegoldenrod}"

    $HeadStyle = $HeadStyle + "</style>"

    $Date = Get-Date

    $DObject | ConvertTo-Html -Head $HeadStyle -Body "<h2>$($ENV:ComputerName) WSUS Report: $date</h2>" | Out-File $ReportLocation -Force

    Send-MailMessage -To $To -from $FROM -subject "WSUS Clean Up Report" -smtpServer $SMTPServer -Attachments $ReportLocation -Port $SMTPPort

  3. Create a scheduled task in Windows that will run the script once a week with a script like this (notice this is tested on Server 2012 R2). You need to modify the –File to the location of the PowerShell script you created above.
  4. $A = New-ScheduledTaskAction -Execute "PowerShell.exe" -Argument ‘-ExecutionPolicy ByPass -File F:\Install\scripts\wsus\Invoke-CleanUPWSUSTASK.PS1’

    $T = New-ScheduledTaskTrigger -WeeksInterval 1 -DaysOfWeek Sunday -At "01:00" -Weekly

    Register-ScheduledTask -User "NT AUTHORITY\SYSTEM" -TaskName ‘WSUS Clean UP TASK’ -Trigger $T -Action $A

  5. From now on, once a week you will receive a nice looking HTML report with information about what has been done


By |2014-09-10T14:38:25+00:00September 10th, 2014|Configuration Manager (SCCM), General info|49 Comments

About the Author:

Kent Agerlund
Microsoft Regional Director, Enterprise Mobility MVP. Microsoft Certified Trainer and Principal consultant. I have been working with Enterprise client management since 1992. Co-founder of System Center User Group Denmark in 2009. Certified MCITP: Enterprise Administrator, MCSA+Messaing, and much more. Member of: Microsoft Denmark System Center Partner Expert Team The Danish Technet Influencers program System Center Influencers Program.


  1. Joe September 17, 2014 at 0:37 - Reply

    I’ve always heard that WSUS was something you set and forget, but I guess you are mostly referring to the database and not the WSUS Console so much. Do the SCCM maintenance taks not clean up anything in the WSUS database?

  2. Jared September 19, 2014 at 18:06 - Reply

    Wow! What an amazing resource this has been. My WSUS was sick. Very sick. I have not been maintaining it well and the server cleanup wizard could attest to that.

    I ended up having to delete all of the old updates via the SQL script and then the cleanup wizard was able to finish. I also ran wsusutil reset and that seems to have me back up and running again!

    Thank you so much for publishing this. I now have a SQL Agent job on the SQL server for defrag and reindex and a scheduled task on the WSUS server. I see brighter days ahead for my WSUS depolyment.

  3. Andreas September 22, 2014 at 13:02 - Reply

    Hi Kent
    I hade to add the Line
    [reflection.assembly]::LoadWithPartialName(“Microsoft.UpdateServices.Administration”) | out-null

    befor I could Create the WSUSConnection

    Thanks for the create Blog!

  4. […] House of Cards–The ConfigMgr Software Update Point and WSUS […]

  5. Jörn Rink October 9, 2014 at 20:15 - Reply

    Hi Kent,
    we have a staged WSUS, one synched with MS, a standalone and our CAS and MP1 and MP2 SCCM ones.

    Of course, we had exactly the described problem on our WSUS on MP1. Great script.

    The only thing is copy and paste, in my german firefox, the ‘ translated to ` or ´. After changing them, the script worked. First in our test environment of course.

    Thanks and greetings from germany,


  6. Alex October 13, 2014 at 13:13 - Reply

    Hi Kent,

    first of all I want to thank you for this amazing article and thanks to Kaido for his script work!

    I’ve got two questions:
    You wrote “In order to fix that, we increased the memory on the Software Update Point and also configure the WSUS App pool memory limit with 8-12 GB.”
    With “we increased the memory on the Software Update Point” you mean for the server, right? Or is there any possibility to change this for the ConfigMgr Role?

    2nd: The Powershell Commandlets are only available from Server 2012 (and higher). Is there any way to do this with powershell and server 2008R2?

    thanks in advance and regards

  7. Kent Agerlund October 13, 2014 at 19:33 - Reply

    It also Works for Windows 2008 R2, just change the first line in the script to
    Add-Type -Path “$Env:ProgramFilesUpdate ServicesApiMicrosoft.UpdateServices.Administration.dll”

    And yes it is the memory on the SUP/WSUS server

  8. Software House October 14, 2014 at 11:56 - Reply

    Excellent post however I’d be very grateful if you could elaborate a little bit more updated. this article offers pleasant understanding yet.Thanks

  9. zbWinMan October 16, 2014 at 17:57 - Reply

    Thanks for this info. I have walked into a SCCM2012/Wsus environment that was previously setup. I know nothing has been cleaned up. I started looking for the SUSDB, but I cannot find any DB other than the CM-DB. Is there a chance the Wsus was installed using WID and not SQL DB? How can I check this? Or how do I find the SUSDB? Is there a setting in the WSUS console that points to the DB? Thanks

  10. Lee October 22, 2014 at 20:30 - Reply

    We have a CAS and Primary site. Which site should I run the WSUS Server cleanup utility first?

  11. ben dwyer October 29, 2014 at 1:09 - Reply

    Hi Kent I noticed in your example where you invoke WSUSSeverCleanup you do not use -CleanupObsoleteComputers. Yet in the script it is used. Do you recommend running -CleanupObsoleteComputers or does ConfigMgr take care of obsolete computers?
    Thanks, Ben

  12. Morten November 19, 2014 at 14:27 - Reply

    Hi Kent

    If the DB is running of a Windows Internal Database, the Query doesn’t Work.
    If you have a WID then you can use this script instead, this Works.


    IF object_id(‘tempdb..#MyTempTable’) is not null DROP TABLE #MyTempTable
    create table #MyTempTable (
    LocalUpdateID int
    IF object_id(‘tempdb..#MyTempTable1’) is not null DROP TABLE #MyTempTable1
    create table #MyTempTable1 (
    LocalUpdateID int


    insert INTO #MyTempTable1 (LocalUpdateID)
    EXEC susdb.dbo.spGetObsoleteUpdatesToCleanup
    select * from #MyTempTable

    insert into #MyTempTable select top (2000) * from #MyTempTable1

    DECLARE @Count INT
    SELECT @Count = COUNT(*) FROM #MyTempTable

    SELECT @msg = ‘Number of updates to be deleted:’ + CAST( @Count AS VARCHAR(10))

    declare c1 cursor local static for
    select * from #MyTempTable
    open c1
    fetch c1 into @x
    while @@FETCH_STATUS = 0

    SELECT @msg = ‘Deleting update with ID:’ + CAST (@x AS VARCHAR(10))
    EXEC spDeleteUpdate @localUpdateID=@x

    fetch c1 into @x
    close c1
    deallocate c1
    SELECT @msg = ‘Deletion completed’

    • alasdaircs December 23, 2014 at 16:18 - Reply

      You, sir, know what you are doing.

      I agree with the insert … exec and the local cursor entirely. The only improvement I would make would be to use table variables to avoid the messy checks at the top and to use the old “while 1=1 begin fetch …; if @@fetch_status 0 break;” trick to save typing the fetch statement twice.

      Good work.

      Alasdair C-S

  13. JeffB February 12, 2015 at 22:38 - Reply

    Kent, you have been a gold mine for years thru your books and these posts. Thank you very much! One of my Win2012/sccm2012 primary sites WSUS/SUP died this last second Tuesday, 40 minutes after syncing with MS. I discovered the failure the next morning when my testing group report showed no successes. I spent 12 fruitless hours yesterday, then found this post this morning.

    The site isn’t up yet, but the script is running s l o w l y. The initial spGetObsoleteUpdatesToCleanup showed 11,797 updates. We are waiting for the first 2000 to complete this evening. I’ll up that number to 3000 once it completes and we should be back up and ready to push updates tomorrow evening. It looks like it deletes about 1000 every 4 hours.

    Thank you again!

  14. Sushain Kapoor February 13, 2015 at 8:08 - Reply

    Just a little clarity, will the client download information for a particular product or all the products selected in wsus.

    Eg: Windows 7 systems will download the catalog information of Windows 7 only against selections made in WSUS. Or it will download the windows XP and windows vista and other product information also. This particular question i am asking as i see only 300 updates as found in windowsupdate.log and not the thousands synced in WSUS

    Is the update metadata exported using the wsusutil.exe the actual catalog size what clients are downloading

  15. Bala Inampudi May 27, 2015 at 18:34 - Reply

    Excellent post

  16. […] The first issue is the WSUS app pool crashing when overloaded. Kent has an awesome post this issue so I don’t need to rehash that here: House of Cards–The ConfigMgr Software Update Point and WSUS. […]

  17. applechaty December 15, 2015 at 15:41 - Reply

    We have a similar issue with the network being flooded as soon as the initial synchronization is done with Microsoft update. We built a brand new SUP and installed WSUS on the same server. As soon as the initial sync completes the clients all seem to connect to the SUP and kill the network. We have seen a few of wuahandler errors however the biggest problem is the impact on the MPLS network. Do you know of anyway to prevent the massive MPLS network increase connecting to the SUP? Can it be throttled or something to try and prevent the network being over utilized? Thanks in advance any suggestions are welcome.

  18. applechaty December 15, 2015 at 15:42 - Reply

    Sorry meant Kent!

  19. […] the past we used the guide from Kent Agerlund (MVP) for cleaning up the WSUS Database in ConfigMgr, also called the house of cards! This guide is still very popular and of course […]

  20. […] Bonus link : I suggest that you read the excellent article written by Kent Agerlund on how to avoid what he calls the House of Cards […]

  21. Steven Jordan January 12, 2016 at 19:53 - Reply

    Hi Ken, your SQL script didn’t work for my WSUS server -at first. Syntax issues and compatibility issues with SQL Express. Are you using the full SQL version with your WSUS server?

    We made some modifications and posted it on my blog: I gave you credit for identifying the problem and for original script.

    Added bonus, the script provides each update ID after each process/ deletion. The update messages are useful because of the long time it takes the script to delete everything. Thanks!

  22. Lee February 25, 2016 at 14:06 - Reply

    Ran the spGetObsoleteUpdatesToCleanup procedure on my SUSDB and appears I have 24,324 updates to delete!!! Might be why it’s not very responsive and constantly times out.

    I’m using the script at the moment to delete these – gonna take a while I reckon!

  23. Curt R March 2, 2016 at 21:41 - Reply

    We are running this on an environment with a CAS and 3 child primary sites. When we get an email telling us the task has run, the only server that shows any values in the cleanup report is our CAS SUP. The columns for SupersededUpdatesDeclined, Expiredupdatesdeclined, etc. are blank in the emails from the SUP servers at the child primary site.

    Is this expected behavior?


  24. […] recent but increasingly frequent issue that was called out by Kent Agerlund in his blog post “House of Cards – The ConfigMgr Software Update Point and WSUS” (referred to all week as “the house of cards post”). This issue seemed to be […]

  25. Devon Dieffenbach June 3, 2016 at 16:43 - Reply

    Not sure about you guys, but I wrote some exception handling around the wsus cleanup to force it to keep going when it encounters the timeout.
    function Recursive-Cleanup(){
    Invoke-WsusServerCleanup -CleanupObsoleteUpdates
    Invoke-WsusServerCleanup -CleanupObsoleteComputers
    Invoke-WsusServerCleanup -CleanupUnneededContentFiles
    Invoke-WsusServerCleanup -DeclineExpiredUpdates
    Invoke-WsusServerCleanup -DeclineSupersededUpdates
    catch [System.Data.Common.DbException]{
    Write-Host -foreground Red “$TimeoutCount `tSQL TIMEOUT Exception. Retrying”

  26. Thomas Forsmark Sørensen July 27, 2016 at 9:30 - Reply

    Is it still necessary to run the “WSUS Server Cleanup process” powershell script after 1602 where the “Run WSUS Cleanup Wizard” was added to ConfigMgr?

  27. Ronnie September 16, 2016 at 11:25 - Reply


    I’ve this issue at a client. When I open up SQL there is no database named SUSDB, but looking in HKEY_LOCAL_MACHINESOFTWAREMicrosoftUpdate ServicesServerSetup that is the name of the server. How can I connect to the database and run the exec spGetObsoleteUpdatesToCleanup and all the other commands?


  28. Roger Truss October 31, 2016 at 18:27 - Reply

    Hi Kent. I am revisiting our WSUS/ConfigMgr integration and working on cleaning things up as it has been YEARS since this has been done.

    One server is using WID and the others are on Secondary servers with full SQL 2008 R2. When running the scrip on my secondary servers I get this.. I am by no means a SQL guru so forgive me in advance but does this mean my DB is too big? The file is only 10.4 GB?

    Thanks in advance!

    Msg 1105, Level 17, State 2, Procedure trOnDeletingDeployment, Line 73
    Could not allocate space for object ‘dbo.tbDeadDeployment’.’PK__tbDeadDe__5EF8D7176C190EBB’ in database ‘SUSDB’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

  29. farnzy December 29, 2016 at 17:12 - Reply

    The script from Henrik Rading works great. I had a devil of a time getting it to run in my setup though. I am working on a local server with 4 instances. I continually received the following error:

    OLE DB provider “SQLNCLI11” for linked server “NULL” returned message “Login timeout expired”.
    OLE DB provider “SQLNCLI11” for linked server “NULL” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”.
    Msg 53, Level 16, State 1, Line 21
    Named Pipes Provider: Could not open a connection to SQL Server [2].

    After searching the web for a couple days, I made the following change to the OPENROWSET line and it connected and ran like a champ.

    FROM OPENROWSET(‘SQLNCLI’, ‘Server=(local);Trusted_Connection=yes;’, ‘EXEC susdb.dbo.spGetObsoleteUpdatesToCleanup’)

    Hope this helps someone in the future.

    • farnzy December 29, 2016 at 17:15 - Reply

      Sorry, my previous entry removed a critical part of the OPENROWSET statement. Lets pretend my instance was named MIKEY

      FROM OPENROWSET(‘SQLNCLI’, ‘Server=(local)MIKEY;Trusted_Connection=yes;’, ‘EXEC susdb.dbo.spGetObsoleteUpdatesToCleanup’)

      The key is to include (local)instance name.

  30. brachus December 30, 2016 at 13:53 - Reply

    Is this still needed with CM CURRENT BRANCH?

    • Kent December 30, 2016 at 14:02 - Reply

      ConfigMgr CB do only cleanup Expired updates – if you want to manage superseded then Yes

  31. F*SUS March 16, 2017 at 18:40 - Reply

    Surcoming the short of MS to make working poducts OOB, i found following query very useful for deleting “unused” updates manually through SQL Management Studio.

    First you Need to connect to your SUS DB (WID or SSEE) depending on your Version, and run the following script (depending on amount of ObsoleteUpdates this query may take some time):

    IF object_id(‘tempdb..#tmpdelUpdates’) is not null DROP TABLE #tmpdelUpdates

    CREATE TABLE #tmpdelUpdates
    LocalUpdateID INT

    INSERT INTO #tmpdelUpdates
    exec spGetObsoleteUpdatesToCleanup;

    DECLARE @myLocalUpdateID int

    SELECT DISTINCT LocalUpdateID from #tmpdelUpdates

    PRINT @myLocalUpdateID
    exec spDeleteUpdate @localUpdateID=@myLocalUpdateID
    DROP TABLE #tmpdelUpdates

    Nice Thing here: It even works with WID where you can’t use “Adhoc Distributed Queries”…

  32. […] See detailed explanation of why this issue occurs, see the article written by Kent Agerlund. […]

  33. Mohamad Rahim May 22, 2017 at 13:53 - Reply

    “I have seen any number from 100 to 8,000”
    Well hallelujah, I feel special, as I found 13087 obsolete updates … only.

    • Jerry A. July 10, 2017 at 6:15 - Reply

      I am right there with you. After unsuccessfully running the Server Cleanup cmdlets a couple of times this afternoon, I am down to 13,050 obsolete updates.
      I was able to get the database re-indexed, but it still would time out. So after seeing I was down to 13,050 (not sure how many there were to start with), I switched to using the SQL script mentioned above for the WID database. Unfortunately, with the length of time that this is going to take, I’ll have to break this into smaller batches and just do a little bit each day. I can’t have the WSUS services be non-responsive for too long, as I still have SCEP definitions to synchronize and deploy, and nightly routine software updates for retail stores that I support.

      Mohamad, were you able to eventually get your 13,087 obsolete updates down to a manageable number?

      Does anyone recommended to re-index the database after completing a cleanup operation? Just wondering if there is a lot of fragmentation due to the cleanup activity.

  34. Thanks June 6, 2017 at 15:23 - Reply


  35. Mike July 11, 2017 at 0:45 - Reply

    HI! SBS2011/WSUS 3.2 environment. Over 80,000 updates clogging up my system. None of these scripts are working for me (report “0 lines affected”, and stuff like that). Any suggestions? Thanks!

  36. Mike July 11, 2017 at 0:46 - Reply

    Also – the powershell commands don’t work at all. I assume they’re for a newer version?

  37. Dustin July 27, 2017 at 16:03 - Reply

    What kind of maintenance is needed on down-level WSUS instances?

  38. Andrew September 13, 2017 at 22:17 - Reply

    Are any of these steps still required/recommended now that the option to clean up WSUS is available in SCCM CB (1702)?

    • Dustin H October 25, 2017 at 0:46 - Reply

      Have you tried it? I came upon this but I am on 1706.

  39. Dallan September 24, 2017 at 18:23 - Reply


  40. […] House of Cards–The ConfigMgr Software Update Point and WSUS […]

  41. Alan Dooley December 15, 2017 at 17:27 - Reply

    Thanks for this. Had 14,000 updated to remove! It took 1h 43m to delete 200 of them so it is going to be a rather long execution!!

  42. […] Doing Software update deployment and not doing regular maintenance will bring your server to a non-functioning state. […]

Leave A Comment