locked
WSUS hangs on Cleanup wizard for Unused Updates, then disconnects from console. RRS feed

  • Question

  • I am running WSUS 3.0 SP1 on Windows 2003 and recently have found that running the Cleanup Wizard works with all of the selectable options except the "Unused Updates" option. It hangs about 1\2 way through and then disconnects from the console. Up until now it has worked.
    I am using the Windows Internal Database and the size of the MDF is 3193024kb.
    Everything other than this wizard is working fine. Please help.
    Monday, October 19, 2009 5:28 PM

Answers

  • This issue has been plaguing a lot of people.  The answer "You have too many updates" is not an answer.  Digging in to find out the real problem, now that's an answer!  At this point, I have two WSUS servers at two locations that have been running for eight days before they finished cleanup.  They have over 9000 files an 100GB at each site.  However, cleanup doesn't delete anything.  So I have tried a wsusutil reset.  That has been running for 5 days now without any indication that it is going to complete.

    I started digging in to find why this is happening.  It appears that the problem stems from SQL.  The sqlserver.exe process runs at 100% CPU with little to no bytes of disk movement.  That usually indicates a poorly performing index.  So I pulled up the worst performing queries, and found the following query keeps coming up as taking all the CPU.  I'm looking to see if I can't find an index that will make this run at the intended speed.  If we can find the answer to this, it will help WSUS to run through a reset in a few minutes instead of an entire week:

    UPDATE tbDeployment SET DeploymentStatus = 0 FROM tbDeployment
        INNER JOIN tbRevision ON tbRevision.RevisionID = tbDeployment.RevisionID
        WHERE tbRevision.RowID = @rowID AND tbDeployment.ActionID = 0
            AND tbDeployment.DeploymentStatus = 1 AND tbDeployment.UpdateType NOT IN ('Category', 'Detectoid')

    Wednesday, March 13, 2013 8:10 PM
  • After much troubleshooting with the SQL profiler, I was able to pinpoint three poorly performing queries (including the one above in my previous post).  Actually, they are as optimized as they can be, considering they are joining across three tables with WHERE clauses on two of the tables.  Each time the query is run it only consumes about 16ms of CPU time.  The problem lies when the query is run hundreds of thousands, if not millions of times, which causes the CPU to run up to 100% and sit there with very little disk byte throughput.

    To counteract this I reduced the level of normalization between the tables.  For example, I created a RowID field in the tbDeployment table and updated it to match the tbRevision table, created an index on it, then modified the stored procedure to remove that portion of the join.  I had to do this for three separate queries, modifying three separate stored procedures.

    By the time I was done, the WSUS reset process took about 10 minutes from start to finish on a 6GB database with 100GB of update files!

    After the reset completed, I removed my indexes, columns, and put the stored procedures back the way they were.

    My WSUS server is running beautifully now.



    • Proposed as answer by Brain2000 Thursday, March 14, 2013 4:49 PM
    • Edited by Brain2000 Thursday, March 14, 2013 4:50 PM
    • Unproposed as answer by Lawrence Garvin Wednesday, May 29, 2013 2:45 AM
    • Proposed as answer by Timothy Carroll Wednesday, May 14, 2014 6:38 PM
    • Marked as answer by Ben Herila [MSFT] Friday, October 31, 2014 8:24 AM
    Thursday, March 14, 2013 4:49 PM
  • > why it appears to be so inefficient I've got not idea

    It's not inefficient -- it just takes that long to scan that much data if the SCW has not been run in a while. The particular step of the SCW that causes the hang is the option "[Delete] Unused updates and update revisions", and this delete decision is based upon updates that are expired and have not been approved for at least 30 days, and update revisions (other than the latest) that have not been approved for at least 30 days.

    In order to evaluate that criteria, the query has to filter the entire catalog of updates for only those that are expired, join that list of updates with every computer group defined, retrieve the Approval Date for the update for each group (because there might be different approval dates -- like between TEST and PRODUCTION groups), determine if each Approval Date is older than 30 days, and then DELETE that update from the database (which will then trigger all of the necessary index modifications that happen anytime you delete a row from an indexed table).

    THEN, it has to repeat all of that for any update *revisions* in the catalog, but filtering for only those that are not the current revision, join that list of revisions with every compuer group defined, retrieve the Approval Date for the update for each group, determine if the Approval Dates are all older than 30 days, and then DELETE that revision from the database.

    If this process hasn't been run in a few months, then depending on how many updates and revisions are in the catalog, and how many computer groups are defined on the server, and how many actual Approval events were recorded against each of those updates and revisions -- it's going to take some time.

    My suggestion is that the Server Cleanup Wizard should be run at least once a month, and the soonest after Patch Tuesday the best (like Wednesday morning is good). If the WSUS server is synchronizing Defender or Forefront updates, then the Server Cleanup Wizard should be run *weekly*. (If there's any doubt, go survey how much content is downloaded into the Definition Updates classification in any given week.)
    Lawrence Garvin, M.S., MCITP:EA, MCDBA
    Principal/CTO, Onsite Technology Solutions, Houston, Texas
    Microsoft MVP - Software Distribution (2005-2009)
    My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
    My Blog: http://onsitechsolutions.spaces.live.com
    Thursday, October 29, 2009 6:39 AM

All replies

  • Strange...now it is working again. I let it run for a few min and then hit cancel. It showed me where it deleted 76 unused revisioins. Then it ran fine.
    Monday, October 19, 2009 5:36 PM
  • I've experienced it and seen it widely reported that the WSUS Cleanup Wizard can take many hours to complete depending on the size of the database and time since last run. During this time CPU activity can be very high on the server and WSUS database can become unresponsive which can make it appear hung, disconnect console,etc...  Kind of a horrible process - why it appears to be so inefficient I've got not idea.  Reports of it taking 4-8hrs to complete aren't that uncommon. It will usually run faster on subsequent attempts.  I've never seen a reason nor "fix" for it taking so long and taxing resources on the server so severely. If anyone knows of any please post.
    Thursday, October 29, 2009 12:20 AM
  • > why it appears to be so inefficient I've got not idea

    It's not inefficient -- it just takes that long to scan that much data if the SCW has not been run in a while. The particular step of the SCW that causes the hang is the option "[Delete] Unused updates and update revisions", and this delete decision is based upon updates that are expired and have not been approved for at least 30 days, and update revisions (other than the latest) that have not been approved for at least 30 days.

    In order to evaluate that criteria, the query has to filter the entire catalog of updates for only those that are expired, join that list of updates with every computer group defined, retrieve the Approval Date for the update for each group (because there might be different approval dates -- like between TEST and PRODUCTION groups), determine if each Approval Date is older than 30 days, and then DELETE that update from the database (which will then trigger all of the necessary index modifications that happen anytime you delete a row from an indexed table).

    THEN, it has to repeat all of that for any update *revisions* in the catalog, but filtering for only those that are not the current revision, join that list of revisions with every compuer group defined, retrieve the Approval Date for the update for each group, determine if the Approval Dates are all older than 30 days, and then DELETE that revision from the database.

    If this process hasn't been run in a few months, then depending on how many updates and revisions are in the catalog, and how many computer groups are defined on the server, and how many actual Approval events were recorded against each of those updates and revisions -- it's going to take some time.

    My suggestion is that the Server Cleanup Wizard should be run at least once a month, and the soonest after Patch Tuesday the best (like Wednesday morning is good). If the WSUS server is synchronizing Defender or Forefront updates, then the Server Cleanup Wizard should be run *weekly*. (If there's any doubt, go survey how much content is downloaded into the Definition Updates classification in any given week.)
    Lawrence Garvin, M.S., MCITP:EA, MCDBA
    Principal/CTO, Onsite Technology Solutions, Houston, Texas
    Microsoft MVP - Software Distribution (2005-2009)
    My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
    My Blog: http://onsitechsolutions.spaces.live.com
    Thursday, October 29, 2009 6:39 AM
  • Thanks for your repies. I regulary run this on a monthly basis. I think I will chalk it up to the fact that this months updates were a record month of updates and just took too long. Thanks.
    Thursday, October 29, 2009 3:12 PM
  • Interesting that this happens again. This time it only gets to the halfway mark and then I get the message that it timed out?? I have run it several times today. It does not seem like it is progressing?? I have been running it almost every week with no issues. I missed last week and now it errors out with a timeout and option to reset server.

     I am wondering if there is anything I can do to fix this. I have been able to run all the other options in the clean up wizard successfully. It is just the unused ones that fail.

     

    Thursday, September 29, 2011 7:57 PM
  • I am wondering if there is anything I can do to fix this.

    Database reindexing and disk defragmenting can play a big part in the Server Cleanup Wizard performance.

    1. Stop the database engine and Update Services service.

    2. Run a full disk defragmentation.

    3. Restart the database engine and run the Database Maintenance script described in the WSUS Operations Guide section Reindex the WSUS Database.

    4. After reindexing, run the Server Cleanup Wizard, and then reindex again after running the Server Cleanup Wizard.


    Lawrence Garvin, M.S., MCITP:EA, MCDBA, MCSA
    Principal/CTO, Onsite Technology Solutions, Houston, Texas
    Microsoft MVP - Software Distribution (2005-2011)
    My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
    My Blog: http://onsitechsolutions.spaces.live.com
    Thursday, September 29, 2011 10:12 PM
  • Well. My dba helped me to try that. It did not help. We are going to try a restore of last months db.
    Monday, October 3, 2011 3:16 PM
  • After the restore the "unused updates" clean up works, but the "deleting computers that have not contacted" is not.
    Monday, October 3, 2011 5:22 PM
  • I found a way to get that to work...by manually deleting the computers. I think it "Hung" because it had so many to delete. The restore was from back in feb.
    Monday, October 3, 2011 5:45 PM
  • This issue has been plaguing a lot of people.  The answer "You have too many updates" is not an answer.  Digging in to find out the real problem, now that's an answer!  At this point, I have two WSUS servers at two locations that have been running for eight days before they finished cleanup.  They have over 9000 files an 100GB at each site.  However, cleanup doesn't delete anything.  So I have tried a wsusutil reset.  That has been running for 5 days now without any indication that it is going to complete.

    I started digging in to find why this is happening.  It appears that the problem stems from SQL.  The sqlserver.exe process runs at 100% CPU with little to no bytes of disk movement.  That usually indicates a poorly performing index.  So I pulled up the worst performing queries, and found the following query keeps coming up as taking all the CPU.  I'm looking to see if I can't find an index that will make this run at the intended speed.  If we can find the answer to this, it will help WSUS to run through a reset in a few minutes instead of an entire week:

    UPDATE tbDeployment SET DeploymentStatus = 0 FROM tbDeployment
        INNER JOIN tbRevision ON tbRevision.RevisionID = tbDeployment.RevisionID
        WHERE tbRevision.RowID = @rowID AND tbDeployment.ActionID = 0
            AND tbDeployment.DeploymentStatus = 1 AND tbDeployment.UpdateType NOT IN ('Category', 'Detectoid')

    Wednesday, March 13, 2013 8:10 PM
  • After much troubleshooting with the SQL profiler, I was able to pinpoint three poorly performing queries (including the one above in my previous post).  Actually, they are as optimized as they can be, considering they are joining across three tables with WHERE clauses on two of the tables.  Each time the query is run it only consumes about 16ms of CPU time.  The problem lies when the query is run hundreds of thousands, if not millions of times, which causes the CPU to run up to 100% and sit there with very little disk byte throughput.

    To counteract this I reduced the level of normalization between the tables.  For example, I created a RowID field in the tbDeployment table and updated it to match the tbRevision table, created an index on it, then modified the stored procedure to remove that portion of the join.  I had to do this for three separate queries, modifying three separate stored procedures.

    By the time I was done, the WSUS reset process took about 10 minutes from start to finish on a 6GB database with 100GB of update files!

    After the reset completed, I removed my indexes, columns, and put the stored procedures back the way they were.

    My WSUS server is running beautifully now.



    • Proposed as answer by Brain2000 Thursday, March 14, 2013 4:49 PM
    • Edited by Brain2000 Thursday, March 14, 2013 4:50 PM
    • Unproposed as answer by Lawrence Garvin Wednesday, May 29, 2013 2:45 AM
    • Proposed as answer by Timothy Carroll Wednesday, May 14, 2014 6:38 PM
    • Marked as answer by Ben Herila [MSFT] Friday, October 31, 2014 8:24 AM
    Thursday, March 14, 2013 4:49 PM
  • This issue has been plaguing a lot of people.  The answer "You have too many updates" is not an answer.

    Sadly, that is exactly the answer.
    They have over 9000 files an 100GB at each site.
    So there's problem Number One. A healthy WSUS server has about 10% of that file consumption. The presense of 100GB of files on a WSUS server is a very strong indication that approvals are not being removed from superseded updates when they should be.
    However, cleanup doesn't delete anything.
    Correct. The Server Cleanup Wizard does not touch updates that have approvals.
    So I have tried a wsusutil reset.
    Why?
    That has been running for 5 days now without any indication that it is going to complete.
    It will, eventually, but your 9000 updates and 100GB of files have presented a lot of reconciliation work for that process to perform.
    I started digging in to find why this is happening.  It appears that the problem stems from SQL.  The sqlserver.exe process runs at 100% CPU with little to no bytes of disk movement.  That usually indicates a poorly performing index.  So I pulled up the worst performing queries, and found the following query keeps coming up as taking all the CPU.  I'm looking to see if I can't find an index that will make this run at the intended speed.
    So my next question would be this: When's the last time you ran the WSUS DB Maintenance script?
    If we can find the answer to this, it will help WSUS to run through a reset in a few minutes instead of an entire week
    Well, no, actually it won't. I've run a 'reset' on a fully reindexed database with only a few thousand updates and a few hundred updates, and it still takes a few hours to complete. Such is the nature of having to tablescan a 60000-row table, cross-check it against target groups and approvals and languages, and then query the filesystem to see if the files that should be present actually are.

    Lawrence Garvin, M.S., MCITP:EA, MCDBA, MCSA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2013)
    My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    Wednesday, May 29, 2013 2:44 AM
  • After much troubleshooting with the SQL profiler, I was able to pinpoint three poorly performing queries (including the one above in my previous post).  Actually, they are as optimized as they can be, considering they are joining across three tables with WHERE clauses on two of the tables.  Each time the query is run it only consumes about 16ms of CPU time.  The problem lies when the query is run hundreds of thousands, if not millions of times, which causes the CPU to run up to 100% and sit there with very little disk byte throughput.

    To counteract this I reduced the level of normalization between the tables.  For example, I created a RowID field in the tbDeployment table and updated it to match the tbRevision table, created an index on it, then modified the stored procedure to remove that portion of the join.  I had to do this for three separate queries, modifying three separate stored procedures.

    By the time I was done, the WSUS reset process took about 10 minutes from start to finish on a 6GB database with 100GB of update files!

    After the reset completed, I removed my indexes, columns, and put the stored procedures back the way they were.

    My WSUS server is running beautifully now.

    Most interesting.

    No doubt the table structures (and SQL code) are not optimally written as some professional DBAs and DB Programmers might expect. Also, in some fairness to the reality -- this SQL code was written almost 10 years ago and most of it was written to SQL8 T-SQL standards, because SQL2000 was all that was available when WSUS was developed. Some of this code was new to WSUS v3, of course, like the Server Cleanup Wizard, so it was written to SQL9 T-SQL standards (in 2006), but dependent upon a database schema with a bit less sophistication.

    Now, what would be really cool is if you would fully document all of the changes you've made, and notwithstanding the fact that implementing the changes threatnes the supportability of an implementation, I'd be very interested in testing those changes on my own WSUS servers of varying vintages, architectures, and performance capabilities.


    Lawrence Garvin, M.S., MCITP:EA, MCDBA, MCSA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2013)
    My MVP Profile: http://mvp.support.microsoft.com/profile/Lawrence.Garvin
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    Wednesday, May 29, 2013 2:49 AM
  • It is sad when it takes longer to clean up, than it would to just nuke the entire database and rebuild from scratch.
    Sunday, January 19, 2014 3:32 AM
  • It is sad when it takes longer to clean up, than it would to just nuke the entire database and rebuild from scratch.

    If you don't change the oil in the car for years and years.. it's probably going to take a few days to rebuild the engine when it blows up.

    If you run the Server Cleanup Wizard monthly, and properly perform approval administration as you should, you'll find that it takes about 10 minutes per month to run the SCW.

    I actually run mine weekly. It took:

    • 22 seconds to decline expired & superseded updates
    • 9 minutes 4 seconds to delete unneeded updates
    • 52 seconds to delete the files associated with those declined/deleted updates


    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    Tuesday, January 21, 2014 10:41 PM
  • So explain to me why MS chose to include a technology that requires that much babysitting in a product like SBS. I have taken on a new client that had been running without any professional IT support for over a year. The group policy had at some point been reconfigured so the desktops weren't even using the WSUS server. The SUSDB was over 20 GB. After wasting about 5 days with various attempts with the cleanup wizard, I gave up trying to clean up the mess and performed the aforementioned 'nuke the DB'. It's a good thing Exchange databases don't behave this poorly when they haven't been professionally maintained.
    Sunday, January 26, 2014 4:46 PM
  • So explain to me why MS chose to include a technology that requires that much babysitting in a product like SBS.
    HA! The real question is why the SBS team tried to hide that complexity in the first place!

    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    Wednesday, February 5, 2014 10:57 PM
  • To counteract this I reduced the level of normalization between the tables.  For example, I created a RowID field in the tbDeployment table and updated it to match the tbRevision table, created an index on it, then modified the stored procedure to remove that portion of the join.  I had to do this for three separate queries, modifying three separate stored procedures.

    By the time I was done, the WSUS reset process took about 10 minutes from start to finish on a 6GB database with 100GB of update files!

    Hi Brain2000, Any chance you would document these changes exactly so us less highly skilled administrators can take advantage of it? : )

    I'd really like to make the changes permanent so WSUS always finishes in 10 mins.

    Saturday, February 15, 2014 12:26 AM
  • I'd really like to make the changes permanent so WSUS always finishes in 10 mins.

    You realize that directly changing the database is not a supported activity, and will likely be undone (if not actually breaking something else) the next time an update to WSUS is released.

    Since the WSUS team is aware of the developing performance issues with the Server Cleanup Wizard "Delete Updates" function, it's highly likely an update in the future WILL change that code. That change may undo your code, or your code may break the new update.

    Truly, it is ever so much easier to just do the mainteance when you're supposed to, and run the SCW when you should. As noted, my SCW completely runs in under 10 minutes without any customized changes!!!


    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    Saturday, February 15, 2014 7:26 PM
  • I'm thinking there probably will never be another update to WSUS, since microsoft is already pushing its flagshit programs like office away from using MSIs, into this click-to-run garbage.

    But with regards to where you said:

    Truly, it is ever so much easier to just do the mainteance when you're supposed to, and run the SCW when you should. As noted, my SCW completely runs in under 10 minutes without any customized changes!!!

    Where is all this stuff I'm "supposed to do" documented?

    Thursday, March 27, 2014 5:40 PM
  • Where is all this stuff I'm "supposed to do" documented?

    For reindexing the database, in the WSUS Operations Guide

    http://technet.microsoft.com/en-us/library/dd939795(v=ws.10).aspx

    For running the Server Cleanup Wizard ... how to use it is documented, but there is no guidance on how often. That really depends on how WSUS is being used.


    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    Thursday, March 27, 2014 10:49 PM
  • The Technet Link is outdated. How has a WSUS 2012 / WSUS 2012 R2 database to be reindexed?

    Franz

    Monday, April 7, 2014 1:27 PM
  • The Technet Link is outdated. How has a WSUS 2012 / WSUS 2012 R2 database to be reindexed?

    The link is not outdated. Aside from some PowerShell enhancements, having native SHA-2 support (which was added to WSUS v3.2 in KB2734608), and being recompiled on .NET4 from .NET2, WSUS v6 is not that much different from WSUS v3.2.

    The principles of reindexing are identical; in fact, the principles of reindexing a database are pretty much universally database agnostic. I'd even venture a guess that the commands are identical. Furthermore, the version of WSUS is really not relevant; what's relevant is the version of the database engine that the database is installed to. (FWIW, WSUS v6 will run just perfectly on a SQL Server 2008 installation, and that script works perfectly on SQL Server 2008.)

    Open the script in SSMS for the version of database you have and do a syntax validation on it before executing it. If it passes the syntax validation, then run it. If you're not comfortable with that, then install a TEST server and run it there first. Somehow, though, I doubt you'll be the first to run a reindex on a WSUS v6 database, and to this date, nobody has reported any issues with the procedure.


    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.


    Monday, April 7, 2014 2:55 PM
  • Why is Lawrence Garvin's reply marked as an answer?  It's not an answer at all to the problem.  It's just stating how to prevent this mess in the first place.  I'm dealing with a WSUS server that WAS cleaned up monthly and still had this issue happen now for reasons unknown and apparently there's no resolution short of Brain2000's brilliant majorly unsupported SQL tweaking.  I wish I had the SQL chops to do what he did because I really don't want to have to stand up a new server just because WSUS's cleanup module is so easily broken.

    Wednesday, May 14, 2014 6:40 PM
  • Why is Lawrence Garvin's reply marked as an answer?

    Because it IS the correct answer!

    It's not an answer at all to the problem.  It's just stating how to prevent this mess in the first place.

    On the contrary, the response provides both the answer to how to deal with the timeouts as well as information on how to prevent them to begin with.

    • To prevent timeouts -- perform maintenance.
    • To deal with the timeout on deleting updates -- continue to restart the SCW when it times out.

    The timeout is caused because there's more work to be done than ASP.NET is willing to wait for, thus *ASP.NET* times out and tells the console its done waiting (and aborts the task). However, all of the previous work done by the SCW is completed. It just needs to be restarted and run again, until all of the necessary work has been completed.

    I'm dealing with a WSUS server that WAS cleaned up monthly and still had this issue happen now for reasons unknown

    I've seen many admins claim to have "cleaned up" the server, and actually accomplished almost nothing at all, because it was not done properly.

    Rather than complain about the answer, perhaps you'd care to share what you've actually done that has not yet worked? And we both can work from there?

    FWIW, I've written a much more detailed (five-part blog series)on this issue at PatchZone.org. See WSUS Timeout Errors: When? and Why? Eliminating and Avoiding


    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    Saturday, May 17, 2014 8:14 PM
  • JaredMagun's technique worked for me.  I started the cleanup, waited about five minutes, cancelled, let it finish.  It showed updates were successfully cleaned up.  I repeated this three times and now the cleanup is working start to finish.

    • Edited by Timothy Carroll Tuesday, May 20, 2014 10:07 PM
    • Proposed as answer by MikeIT440 Thursday, July 31, 2014 1:51 PM
    • Edited by Ben Herila [MSFT] Friday, October 31, 2014 8:20 AM Removed comment about another member
    Tuesday, May 20, 2014 10:05 PM
  • To anyone who still interested on this issue, please review comments on WSUS Cleanup Codeplex Project from user jjdacl and avianwaves:

    They put this script which you can run directly in sql console

    DECLARE @var1 INT 
    DECLARE @msg nvarchar(100) 
    
    CREATE TABLE #results (Col1 INT) 
    
    INSERT INTO #results(Col1) 
    EXEC spGetObsoleteUpdatesToCleanup 
    
    DECLARE WC Cursor 
    FOR SELECT Col1 FROM #results ORDER BY 1
    OPEN WC 
    
    FETCH NEXT FROM WC INTO @var1 
    
    WHILE (@@FETCH_STATUS > -1) BEGIN 
    	SET @msg = 'Deleting ' + CONVERT(varchar(10), @var1) 
    	RAISERROR(@msg,0,1) WITH NOWAIT 
    	EXEC spDeleteUpdate @localUpdateID=@var1 
    
    	FETCH NEXT FROM WC INTO @var1 
    END 
    
    CLOSE WC 
    DEALLOCATE WC 
    DROP TABLE #results

    This procedure is also very slow but at least we can check the progress and, as is running directly in the console doesn't timeout. In my case it took about 40 sec in delete each obsolete update.


    Roberto Figueroa.

    Thursday, June 26, 2014 9:36 PM
  • Agreed.  Not that it's surprising anymore, but this is still sucks......
    Monday, August 11, 2014 9:12 PM
  • This procedure is also very slow...

    Because it's written with a CURSOR.

    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.

    Wednesday, August 13, 2014 12:13 AM
  • It does not run much better when written without a CURSOR. I modified it to use a WHILE loop and it deletes about 1 per minute here which is the same as the old code with a CURSOR.

    use susdb
    DECLARE @msg nvarchar(100)
    DECLARE @NumberRecords int, @RowCount int, @var1 int
    
    -- Create a temporary table with an Identity column
    CREATE TABLE #results (RowID INT IDENTITY(1, 1), Col1 INT)
     
    -- Call the Stored Procedure to get the updates to delete & insert them into the table
    INSERT INTO #results(Col1) 
    EXEC spGetObsoleteUpdatesToCleanup 
    
    -- Get the number of records in the temporary table
    SET @NumberRecords = @@ROWCOUNT
    SET @RowCount = 1
    
    -- Show records in the temporary table
    select * from #results
    
    -- Loop through all records in the temporary table
    -- using the WHILE loop construct & call the Stored Procedure to delete them
    WHILE @RowCount <= @NumberRecords
    BEGIN
     SELECT @var1 = Col1 FROM #results where RowID = @rowcount
    SET @msg = 'Deleting UpdateID ' + CONVERT(varchar(10), @var1) + ', Rowcount '+ CONVERT(varchar(10), @rowcount)
    	RAISERROR(@msg,0,1) WITH NOWAIT 
     EXEC spDeleteUpdate @localUpdateID=@var1 
     SET @RowCount = @RowCount + 1
    END
    
    -- Drop the temporary table when completed
    DROP TABLE #results
    Joe Powers
    Tuesday, August 26, 2014 3:04 PM
  • Thanks Joe. Here's an example. I had rebuilt a sbs2011 box and forgot the reinstate the wsus cleanup script I had put in place earlier. The wizard wouldn't run even after reindexing the db. This script is now running and will take nearly 6 days to complete at your 1 / minute timing.
    Tuesday, September 2, 2014 7:19 PM
  • It does not run much better when written without a CURSOR. I modified it to use a WHILE loop and it deletes about 1 per minute here which is the same as the old code with a CURSOR.

    Not surprised. To be frank, using a WHILE LOOP just turns it into a manual CURSOR, making it no better off than using the CURSOR construct in T-SQL. The correct approach here is to write the code with a SET-BASED approach. Unfortunately, as you're about to see.. sometimes there are scenarios just too complex to do with a set-based approach.

    In order to use the set-based approach, you'll need to take the code from spDeleteUpdate and modify it to join directly to the table of GUIDs to delete. Execute the whole thing in *one* DELETE statement.

    Of course, part of the other problem that contributes to the performance issues is that spDeleteUpdate *ALSO* uses CURSORs.... <sigh>, and a subprocedure. spDeleteUpdate retrieves a list of RevisionIDs, and then calls spDeleteRevision for each of those RevisionIDs. (And as we've already seen, some updates may have a large number of RevisionIDs.) spDeleteRevision is where the real work is done.

    At the crux of that proc, are deletes from these tables:

    • dbo.tbEventInstance (this will be a time killer as well, depending on how many clients affected a given update, and how much activity surrounded that update)
    • dbo.tbDeployment
    • dbo.tbUpdateStatusPerComputer
    • dbo.tbProperty
    • dbo.tbFlattendedRevisionInCategory
    • dbo.tbRevisionInCategory
    • dbo.tbRevisionLanguage
    • dbo.tbMoreInfoURLForRevision
    • dbo.tbSecurityBulletinForRevision
    • dbo.tbKBArticleForRevision
    • dbo.tbBundleDependency
    • dbo.tbBundleAtLeastOne
    • dbo.tbBundleAll
    • dbo.tbPrerequisiteDependency
    • dbo.tbInstalledUpdateSufficientForPrerequisite
    • dbo.tbPrerequisite

    And all of the associated transaction wrappers and error checking for each and every deleted row from each of those tables to ensure continued data integrity in the database.

    So while the code above making a simple call to spDeleteUpdate looks quite simple on the surface... underneath the covers there's a LOT of activity going on to make that one update get deleted.

    If you wanted to speed it up... you'd need to use spGetObsoleteUpdatestoCleanup to get a list of UpdateIDs, then build a list of RevisionIDs from that, and then join that temp table of RevisionIDs to each of the -15- tables above, and delete from all -15- tables simultaneously. Which would force all of those deleted records into a SINGLE transaction, and probably whatever gains you got from switching from procedural logic to set-based logic would be lost in the massive overhead of managing a transaction of that size.

    A possible compromise would be to execute a set-based DELETE by UpdateID, so you'd capture the RevisionIDs for a given UpdateID, and then delete across all -15- tables for that short list of RevisionIDs (except where that list ain't so short). But still, this compromise might well speed things up, as the loop for UpdateIDs is likely only measured in the hundreds, where as the loop through all RevisionIDs is sometimes measured in the thousands or tens of thousands (depending on if/when the server was last purged).


    Lawrence Garvin, M.S., MCSA, MCITP:EA, MCDBA
    SolarWinds Head Geek
    Microsoft MVP - Software Packaging, Deployment & Servicing (2005-2014)
    My MVP Profile: http://mvp.microsoft.com/en-us/mvp/Lawrence%20R%20Garvin-32101
    http://www.solarwinds.com/gotmicrosoft
    The views expressed on this post are mine and do not necessarily reflect the views of SolarWinds.




    Wednesday, September 3, 2014 2:26 PM
  • This is all well and good, but there is a twist to this issue when it comes to my issue.  While running the Cleanup wizard, I get

    The description for Event ID 0 from source WSusCertServer cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

    If the event originated on another computer, the display information had to be saved with the event.

    The following information was included with the event:

    Service stopped

    Then I get a .NET error and an .ASP.NET error and WSUS disconnects.

    No cleanup, need to restart WSUS.

    Thursday, October 30, 2014 8:06 PM
  • You realize that directly changing the database is not a supported activity, and will likely be undone (if not actually breaking something else) the next time an update to WSUS is released.

    Since the WSUS team is aware of the developing performance issues with the Server Cleanup Wizard "Delete Updates" function, it's highly likely an update in the future WILL change that code. That change may undo your code, or your code may break the new update.

    My advice is to NOT modify the WSUS database if you're using Windows Internal Database (WID). Modifying the SUSDB schema in any way could cause WID to fail to start due to a tamper-proofing feature that protects the local WID database.

    If you are using a SQL Server database, you are welcome to modify the WSUS database at your own risk. Please do post your findings! We are actually working to improve the perf of wsusutil reset and the Server Cleanup Wizard, among other things.

    FYI, re-running postinstall may fix the WSUS stored procedures and database schema if they get messed up. Also, any changes to the DB schema could be overwritten upon upgrade (or, changes to the DB schema could cause upgrade to fail). So I suggest restoring the original schema before upgrading WSUS.

    Thanks,
    Ben


    Friday, October 31, 2014 8:32 AM
  • Great Stuff ... Thanks Man (or Woman)!

    I had 4085 updates to clean-out! Your script is currently doing the job and quite well. Thank you once again.

    Friday, November 20, 2015 4:13 PM
  • That sounds excellent! How do I do that? :(
    Wednesday, August 3, 2016 1:13 PM
  • To anyone who still interested on this issue, please review comments on WSUS Cleanup Codeplex Project from user jjdacl and avianwaves: They put this script which you can run directly in sql console [...]

    This worked for me as well. I had a server sitting before me, SBS2011, which has been running for 6 years without having run the cleanup wizard once. The query took about 48 hours to finish, so this is something to do over the weekend.

    Some annotations: If the WSUS database resides inside Windows' SSEE database, you have to

    • Right-Click and "Run as Administrator" the SQL Management Studio
    • Paste the following into the Server Name field: \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
    • Right-Click the SUSDB database, "New Query", then paste the quoted SQL code into the query field and run it; watch the progress in the messages tab

    • Edited by EdRoxter Wednesday, December 7, 2016 3:02 PM insert quote
    Wednesday, December 7, 2016 3:00 PM
  • The real cause of timeout is the lack of indexes for procedure spDeleteUpdate/spDeleteRevision.  In the depth:
    DELETE FROM dbo.tbRevisionSupersedesUpdate WHERE SupersededUpdateID = @updateID
    -  No optimized Index for column SupersededUpdateID causes table scan - I have about 110 000 records.
    DELETE FROM dbo.tbLocalizedProperty WHERE LocalizedPropertyID IN (SELECT LocalizedPropertyID FROM @localizedPropertyIDs)-  No index for foreign key on table [dbo].[tbLocalizedPropertyForRevision] over 1 000 000 records.
    Because these queries execute many times for single update, total execution time may be several minutes depending on update and available resources.
    I have WSUS installation on virtual Windows 2016, Internal Database 6GB RAM. WSUS database over 10 GB.   Without indexes single update deletion consumed 32s CPU,  819000 reads, 40s duration (vary depending on update and available resources). With indexes up to 500ms CPU, 40000 reads and usually duration from 110  to 1100ms.

    Creating indexes causes that configuration will be unsupported by Microsoft. I submitted case to Microsoft, maybe Microsoft fixes this in near future.   
    USE [SUSDB]
    GO
    CREATE NONCLUSTERED INDEX [IX_tbRevisionSupersedesUpdate] ON [dbo].[tbRevisionSupersedesUpdate]([SupersededUpdateID])
    GO
    CREATE NONCLUSTERED INDEX [IX_tbLocalizedPropertyForRevision] ON [dbo].[tbLocalizedPropertyForRevision]([LocalizedPropertyID])
    GO


    On Windows 2016 some additional improvement on reads can be achieved with indexes on tables [tbDistributionComputerHardwareId], [tbDriverFeatureScore] on columns [RevisionID], [HardwareID] for foreign table key to dbo.tbDriver. Existing indexes don’t include [HardwareID].

    Affected query: DELETE FROM dbo.tbDriver WHERE RevisionID = @revisionID.
    USE [SUSDB]
    GO
    CREATE NONCLUSTERED INDEX IX_tbDistributionComputerHardwareId ON [dbo].[tbDistributionComputerHardwareId] ([RevisionID],[HardwareID])
    GO
    CREATE NONCLUSTERED INDEX [IX_tbDriverFeatureScore] ON [dbo].[tbDriverFeatureScore] ([RevisionID],[HardwareID])
    GO

    Friday, December 23, 2016 1:03 PM
  • Roberto and Lawrence and Ed Roxter, thank you very very much for this.  I think I might have you all beat here - I am working a SBS2011 server that's been in operation for 8 years and -never- had any maintenance run on WSUS on it at all - reindexing the database got the cleanup wizard to run on everything except for deleting obsolete updates - whereupon it would just abort within a couple minutes.  I'm currently running the delete script in the SQL management studio - and it is doing 1 delete every TWO MINUTES.  There's thousands of obsolete updates to delete - but it -is- deleting them.

    Backup Exec on this thing was taking 6 hours to backup the WSUS database...

    Sunday, March 5, 2017 1:59 AM
  • You're spewing a bunch of nonsense. 

    "You have too many updates"

    Really? Do you realize that *this is what databases are for*?

    "A healthy WSUS server has 10% of that"

    How can you say that without knowing which products they have marked for updating and (more importantly) which classifications they've got ticked?

    "Such is the nature of having to tablescan a 60000-row table, cross-check it against target groups and approvals and languages, and then query the filesystem to see if the files that should be present actually are."

    Um wut? A properly indexed database would fly right through this. 

    This isn't what you're great at, sorry.


    Thursday, March 15, 2018 4:55 PM
  • As noted, my SCW completely runs in under 10 minutes without any customized changes!!!

    For how many clients? For which products? For which classifications? You're just pulling numbers out of thin air.

    Thursday, March 15, 2018 4:58 PM
  • Please have a look at the WSUS Automated Maintenance (WAM) system. It is an automated maintenance system for WSUS, the last system you'll ever need to maintain WSUS!

    https://community.spiceworks.com/scripts/show/2998-wsus-automated-maintenance-formerly-adamj-clean-wsus

    What it does:

    1. Add WSUS Index Optimization to the database to increase the speed of many database operations in WSUS by approximately 1000-1500 times faster.
    2. Remove all Drivers from the WSUS Database (Default; Optional).
    3. Shrink your WSUSContent folder's size by declining multiple types of updates including by default any superseded updates, preview updates, expired updates, Itanium updates, and beta updates. Optional extras: Language Packs, IE7, IE8, IE9, IE10, Embedded, NonEnglishUpdates, ComputerUpdates32bit, WinXP.
    4. Remove declined updates from the WSUS Database.
    5. Clean out all the synchronization logs that have built up over time (configurable, with the default keeping the last 14 days of logs).
    6. Compress Update Revisions.
    7. Remove Obsolete Updates.
    8. Computer Object Cleanup (configurable, with the default of deleting computer objects that have not synced within 30 days).
    9. Application Pool Memory Configuration to display the current private memory limit and easily set it to any configurable amount including 0 for unlimited. This is a manual execution only.
    10. Checks to see if you have a dirty database, and if you do, fixes it. This is primarily for Server 2012 WSUS, and is a manual execution only.
    11. Run the Recommended SQL database Maintenance script on the actual SQL database.
    12. Run the Server Cleanup Wizard.

    It will email the report out to you or save it to a file, or both.

    Although the script is lengthy, it has been made to be super easy to setup and use so don't over think it. There are some prerequisites and instructions at the top of the script. After installing the prerequisites and configuring the variables for your environment (email settings only if you are accepting all the defaults), simply run:

    .\Clean-WSUS.ps1 -FirstRun

    If you wish to view or increase the Application Pool Memory Configuration, or run the Dirty Database Check, you must run it with the required switch. See Get-Help .\Clean-WSUS.ps1 -Examples

    If you're having trouble, there's also a -HelpMe option that will create a log so you can send it to me for support.

    Adam Marshall, MCSE: Security
    http://www.adamj.org
    Microsoft MVP - Windows and Devices for IT

    Friday, March 16, 2018 3:34 AM