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:

clip_image002

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

clip_image004

 

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

Source
System.Windows.Forms

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.

 

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

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

DECLARE myCursor CURSOR FOR
SELECT LocalUpdateID FROM #MyTempTable

DECLARE @x INT
DECLARE @Msg VARCHAR(50)
DECLARE @Count INT
SELECT @Count = COUNT(*) FROM #MyTempTable

SELECT @msg = ‘Number of updates to be deleted:’ +  CAST( @Count AS VARCHAR(10))
RAISERROR(@msg, 0, 1) WITH NOWAIT

OPEN myCursor
FETCH NEXT FROM myCursor INTO @x

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @msg = ‘Deleting update with ID:’ + CAST (@x AS VARCHAR(10))
    RAISERROR(@msg, 0, 1) WITH NOWAIT
    EXEC spDeleteUpdate @localUpdateID=@x
   
    FETCH NEXT FROM myCursor INTO @x
END
CLOSE myCursor;
DEALLOCATE myCursor;
DROP TABLE #MyTempTable;
SELECT @msg = ‘Deletion completed’
    RAISERROR(@msg, 0, 1) WITH NOWAIT

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 = "smtp.viamonstra.com”

    $SMTPPort = 25

    $To = "Kent Agerlund <kent.agerlund@viamonstra.com>"

    $From = "ConfigMgr <configmgr@viamonstra.com>"

    $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

    image


Comments (28):

  1. Joe says:

    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 says:

    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 says:

    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!
    Andreas

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

  5. Jörn Rink says:

    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,

    Jörn

  6. Alex says:

    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
    alex

  7. Kent Agerlund Kent Agerlund says:

    It also Works for Windows 2008 R2, just change the first line in the script to
    Add-Type -Path “$Env:ProgramFiles\Update Services\Api\Microsoft.UpdateServices.Administration.dll”

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

  8. 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 says:

    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 says:

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

  11. ben dwyer says:

    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 says:

    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.

    USE SUSDB

    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
    )

    GO

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

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

    DECLARE @x INT
    DECLARE @Msg VARCHAR(50)
    DECLARE @Count INT
    SELECT @Count = COUNT(*) FROM #MyTempTable

    SELECT @msg = ‘Number of updates to be deleted:’ + CAST( @Count AS VARCHAR(10))
    RAISERROR(@msg, 0, 1) WITH NOWAIT

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

    SELECT @msg = ‘Deleting update with ID:’ + CAST (@x AS VARCHAR(10))
    RAISERROR(@msg, 0, 1) WITH NOWAIT
    EXEC spDeleteUpdate @localUpdateID=@x

    fetch c1 into @x
    END
    close c1
    deallocate c1
    SELECT @msg = ‘Deletion completed’
    RAISERROR(@msg, 0, 1) WITH NOWAIT

    • alasdaircs says:

      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 says:

    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 says:

    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 says:

    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 says:

    Ken,
    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 says:

    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. 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: http://www.stevenjordan.net/2016/01/resolve-wsus-database-error-timeouts.html. 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 says:

    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 says:

    Kent,
    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?

    Thanks.

  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. 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(){
    try{
    Invoke-WsusServerCleanup -CleanupObsoleteUpdates
    Invoke-WsusServerCleanup -CleanupObsoleteComputers
    Invoke-WsusServerCleanup -CleanupUnneededContentFiles
    Invoke-WsusServerCleanup -DeclineExpiredUpdates
    Invoke-WsusServerCleanup -DeclineSupersededUpdates
    }
    catch [System.Data.Common.DbException]{
    $global:TimeoutCount++
    Write-Host -foreground Red “$TimeoutCount `tSQL TIMEOUT Exception. Retrying”
    Recursive-Cleanup
    }
    }
    Recursive-Cleanup

Leave a Reply