none
Alternatives for WSUS-deleting unused updates RRS feed

  • Question

  • Hi, I need to clean up a WSUS db on a SBS 2011. I'm running the "Server Cleanup Wizard". The task "Deleting unused updates" takes forever, and the SQL/IIS processes spikes. Probably beacuse this task never has been run before. I need a quick alternative. The organization doesn't maintain/use WSUS but I rather not uninstall the role on the SBS 2011.

    Thank you!

    Wednesday, June 12, 2013 10:00 AM

Answers

All replies

  • Hi S.Eng,

    I've been in a similar situation in the past where the Server Cleanup Wizard was NEVER run and I couldn't get it to complete. We were trying to get it working again so I don't know of a "quick" way of fixing this (ie. recreating the DB somehow) but what worked for us was running the wizard but only selecting one check box at a time. So start with running the Server Cleanup Wizard against "Unused updates and update revisions", let it complete and rerun the wizard and slowly work your way down the list.

    Also have a look at the "Re-index the WSUS 3.0 Database" script here: http://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61.

    I hope this helps,

    Mark.

    Wednesday, June 12, 2013 11:59 AM
  • Thanks for your reply. I've tried that, choosing only the first option but that still has the same effect (takes forever, CPU-intensive). Will the "Re-Index database" solve my problem?
    Wednesday, June 12, 2013 12:02 PM
  • Using the phased approach to run the cleanup wizard wasn't a magic bullet, it still took a long time but it completed which is what we couldn't achieve running all the steps at once.

    Unfortunately I can't speak from experience and say the reindex script will solve your problem since I didn't know it existed when I faced this problem. I have seen it suggested to run the maintenance script before cleanup in the past though (eg. http://social.technet.microsoft.com/Forums/en-US/winserverwsus/thread/c4feaf5d-6a6d-4e31-b5b6-558bbdf26e78).

    I'm also a bit out of my depth regarding SBS and whether there's anything you need to take into account if you just uninstall and reinstall WSUS but take a look at this article: http://blogs.technet.com/b/sus/archive/2008/10/20/wsus-updates-page-times-out-while-trying-to-view-or-approve-updates.aspx

    Mark.

    Wednesday, June 12, 2013 12:29 PM
  • >Mark said:...but only selecting one check box at a time<

    I agree.  "Deleting unused updates" is the culprit, and once it starts working, you cannot cancel it except through Task Manager.

    Believing that it is hung after a long period of time with no feed back is certainly a logical expectation, and the one that will serve you best most of the time.  The following process will either convince you that there is nothing wrong with your WSUS install, and that the Cleanup Wizard is working properly, or convince you that the Comet Kohoutek will return before it finishes.

    1.  Open the Cleaning Wizard, and uncheck all except the bottom box, and run it.
    2.  It shouldn't take very long, and after it finishes, you will at least know the Cleanup Wizard works.
    3.  Next, repeat the process, one at a time moving up from the bottom, except do not do the top one, "Deleting unused updates".
    4.  If everything has worked well up to this point, you now will have a basis for believing that WSUS and the Cleanup Wizard are actually doing something when we do the next step.
    5.  Now check only the top box, "Deleting unused updates", run it, and come back in an hour.  You should notice a sliver of blue in the progress bar starting. 

    This method allows you to ascertain that progress is being made, and because the progress bar is only graphing "Deleting unused updates",  it enables you to make estimates of when it might finish.  It might work out to be a week, but at least you can monitor the progress, and know whether or not it is hung.

    The very fact that this takes as long as it does, and that maintenance was not designed into WSUS, indicates professional-level effort and thought was not expended in this area.  However, with properly adjusted expectations, we can say that it makes updates manageable, and it's free.
    Tuesday, September 17, 2013 5:04 PM
  • The very fact that this takes as long as it does, and that maintenance was not designed into WSUS, indicates professional-level effort and thought was not expended in this area.  However, with properly adjusted expectations, we can say that it makes updates manageable, and it's free.

    Actually, these issues with running the Server Cleanup Wizard are almost exclusively caused, as both S. Eng and Mark have suggested, by the WSUS Administrators failure to use this tool when it should have been used way-back-when.

    Maintenance IS designed into WSUS. You just need to DO IT.

    Your procedure for checking one box at a time in the Server Cleanup Wizard is unique, but somewhat flawed. The optimal order of "one at a time" execution of the Server Cleanup Wizard is:

    1. Delete unused computers. (If you don't have any, you can skip this step.)

    2. Decline expired updates AND Decline superseded updates. (These two steps can be performed in a single pass and do not involve the addition or removal of data from the database, only the changing of values, so they'll run pretty fast -- unless you have a LOT of expired and superseded updates! Note also that only NotApproved updates can be declined so first you'll need to remove any Approvals configured on these updates.)

    3. Delete unused updates. (Yes, this task can take a very long time. It requires some rather extensive joins to be made in order to determine the criteria for deletion eligibility, and then it has to delete an instance of an update, which actually involves deleting data from several tables, and the associated reindexing as a result of that delete. This is a very expensive task to run. Note also that, like #2 above,  you will need to remove any existing Approvals before the updates can be deleted.)

    4. Delete unused files. (This task will have no impact on the database, but will free up filesystem space based on the updates declined in #2 and deleted in #3.)


    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, September 18, 2013 3:43 PM
    Moderator
  • Hi Lawrence. 
    While technically correct on the order, nobody is going to rationalize through that process each time.  The purpose for working from the bottom to the top is it is easy to remember, and none of the lower steps ever take much time, no matter which order you perform them in.   

    As for my comment on maintenance being designed into WSUS, the intent there was that it shouldn't be something you do not need to do at all.  If we apply the same logic to the operation of WSUS, then WSUS should not run automatically, databases should not automatically use statistics to determine when to index columns, and there are hundreds of maintenance processes run by operating systems and utilities that we should be doing manually.  The expectation is, if it's something that needs to happen all of the time, it shouldn't have to be done at all, which is why the next step people do is schedule the maintenance of WSUS in Task Scheduler.  As with the normal progression of things, it would be logical that a maintenance process will be incorporated into future versions.
    Wednesday, September 25, 2013 1:20 PM
  • While technically correct on the order, nobody is going to rationalize through that process each time.  The purpose for working from the bottom to the top is it is easy to remember, and none of the lower steps ever take much time, no matter which order you perform them in.   

    They don't take much time because they don't actually do any work if peformed out of order! But feel free doing what is *easy* for you.

    Me, myself, I prefer to do, and have employees who do, things the RIGHT way rather than the EASY way. But then I've already had this conversation several other times in this forum over the years, so really no need to hash it all out yet again. :-)

    As for the maintenance...  the scheduling requirement of that maintenance varies from implementation to implementation which is why it's NOT explicity built into the product. Some people may only need to run the WSUS Database Maintenance script on rare occasions; me personally, until I developed an issue with a legacy WSUS server that has a bit too many updates on it (clean up is in progress) -- I never ran the maintenance script, and never needed to. Once I get that legacy server migrated up on a more powerful machine, I don't expect to use that script again.

    In addition, the Server Cleanup Wizard IS designed into WSUS.. right there in the console for anybody and everybody to launch whenever its needed, including the abilty to choose, or not, particular maintenance steps that may or may not be required -- or may or may not even do anything useful, depending on the other "maintenance" steps, such as removing update approvals, that couldn't possibly be designed as a 'tool' because everybody's needs are different, and each approval needs to be evaluated on a per-update basis. And yet, despite it's readily available presence in the console, it's still ignored by the majority of WSUS Admins. Gofigger.

    The inclusion of PowerShell support in WSUS v6 allows you to build a PowerShell script to "decline all superseded updates reported as 100% Installed/NotApplicable". That would be a nice thing to have. You could even schedule that. But what if there's a special case scenario where an admin needs to leave *one* update in NotApproved state rather than Declined? (I can't give you a real-world example, because I honestly don't think there is -- but as a Product Architect I can also tell you that taking away choice from the end-user is always a Bad Thing.) Well.. now you can't use the script and you have two choices: Do it manually, or customize the script. Sometimes, just because something can be automated does not mean that it should be automated!

    Of course, if the "script" is buried inside the program code and not available to the end-user, then you can't modify it at all. There ARE advantages to having the DB Maintenance Script available as an external SQL script, and not "built-in" to the product (or even implemented as a Stored Procedure called from the UI like the Server Cleanup Wizard actually is).

    For example, maybe the specifications of when indexes are rebuilt is to strict, or not strict enough, for a particular environment. By default, the provided script runs a reindex if:

    • Avg Page Space Used <85%, or
    • Avg Fragmentation > 15% when Page Count > 50, or
    • Avg Fragmentation > 80% when Page Count > 10

    Maybe those choices are not optimal for your particular WSUS database? With an external script, as is currently provided, it's trivial for a DBA to change those values. If the script were implemented as a Stored Procedure, you'd be flying off into "unsupported" territory if you modified the script. The point is, by expressly distributing the script outside of the product itself, Microsoft actually empowers you to make customizations to the process as may fit your needs.


    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, September 25, 2013 8:24 PM
    Moderator
  • Thanks, but we're going to have to have to just disagree with you on this one.

    >They don't take much time because they don't actually do any work if peformed out of order!<
    If that were true, why would Microsoft make a wizard that empowers users to check boxes that do nothing?  Moreover, once it's cleaned up, it gets run with all boxes checked in whatever order it happens to run them.

    >As for the maintenance...  the scheduling requirement of that maintenance varies from implementation to implementation <
    That could be said for any process automatically run by the operating system, directory service, or DBMS.  Currently scheduled processes often find little or nothing to do.  Microsoft doesn't require users to run these on different schedules to optimally match the implementation because there is very seldom an ROI for optimal.  WSUS maintenance is no exception.  Especially in the case of SBS, designed for small businesses that have no dedicated IT, no justification is possible for something that grows useless data by the gigabyte, and requires the knowledge of an IT person they don't have, to discover and run a manual wizard and DB script.  Having been a CTO at two large multi-state tier-1 automotive suppliers over 25 years, even at the enterprise level, IT departments don't do anything manually that can be automated.  It doesn't make sense fiscally or from a human factors standpoint.  As our witness, the Internet has many threads about this shortcoming and steps to work around it.

    Thursday, September 26, 2013 4:17 PM
  • >They don't take much time because they don't actually do any work if peformed out of order!<
    If that were true, why would Microsoft make a wizard that empowers users to check boxes that do nothing?

    This is actually a fair question. Because the tasks are not executed in the order of visual presentation in the dialog. The tasks ARE performed in the optimal order when more than one task is selected.

    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.

    Friday, September 27, 2013 9:59 PM
    Moderator