none
Getting past WSUS Cleanup Wizard time out, removing unnecessary updates.

    General discussion

  • Standard problem with WSUS Cleanup Wizard timing out without removing any unnecessary updates. SBS2K8. WSUS 3. 6000+ unnecessary updates awaiting approval. Huge database. Unresponsive SQL server. Lot of people have this problem:
    http://social.technet.microsoft.com/Forums/en-US/9724778f-c1a0-4d24-82e4-8b2d054257d6/wsus-hangs-on-cleanup-wizard-for-unused-updates-then-disconnects-from-console?forum=winserverwsus

    Drives are regularly defragged in the background. Not gonna shut down the server and have down time just to fix this.

    Tried the reindexing script from
    http://technet.microsoft.com/en-us/library/dd939795(WS.10).aspx
    , (don't copy the command line, it has weird characters in it, just type it in manually.) and it completes but it didn't improve anything.

    Found this:
    http://wsus.codeplex.com/releases/view/17612
    and it also times out.

    Found this comment:
    For anyone who is getting Timeout Expired with obsolete updates. I have a solution! Use server name : "
    \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
    " to connect with SQL managment studio. Once connected manually run "
    exec spGetObsoleteUpdatesToCleanup
    ". This will return a list of obsolete ID's. For each one run "
    exec spDeleteUpdate @localUpdateID=000000
    ", where 000000 is the ID. For myself I found the first ID in the list took a full 37 minutes to delete and then after that I could run the cleanup through the GUI as per usual.

    by jjdacl on Apr 23 at 12:55 PM 

    Found that you actually need to do:
    USE SUSDB
    GO
    exec spGetObsoleteUpdatesToCleanup

    And to connect in the first place, I had to hit Options, and select named pipe from the middle pull down. 

    First delete took 6 minutes and memory has spiked up to almost 15GB in use out of 16GB physical. But the WSUS console (Update Services) still shows the same number of old updates. Failure? I don't think so: I'm running the cleanup wizard again and so far it isn't timing out... It has run over night, and has made some progress; the bar has moved perhaps 5%. 

    So... My take is that the SQL server causes this problem when there is to much data, because the indexes are poorly designed (not because the indexes need to be re-indexed) causing the first query to time out, which causes the cleanup to fail. Once you get past that first deletion, and have everything loaded into memory, the cleanup tool can stay connected long enough to delete each unneeded update.

    Next step will be to find a command line method, such as:
    http://wsus.codeplex.com/releases/view/17612
    and put it into the task scheduler like Microsoft should have done at the beginning to keep this from getting out of hand.

    P.S. Lawrence Garvin, please do NOT reply to this; I have no need of your arrogance.

    Friday, August 01, 2014 3:10 PM

All replies

  • Here is a screen shot of the Studio Express setup. May help. The lower right corner is first, then the upper right for each entry in the results. I only did the first two, and the second one was very fast, so I tried the cleanup wizard again.

    Friday, August 01, 2014 6:23 PM
  • James,

    You are a life saver! My SBS 2011 WSUS server has been "constipated" for years. I followed your advice, cleaned out the first 11 updates manually, and now the cleanup wizard seems to be working properly. Each time I query for obsolete updates in SQL now, fewer and fewer pop up.

    For those less experienced in SQL, here is the process:

    1. Enable "piped pipes" access for the Microsoft##SSEE database. Access wasn't enabled on mine initially.

    2. Open management studio and enter "\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query" under server name.

    3. Click "options" and select "Named Pipes" under "Network protocol" under the "Connection Properties" tab

    4. Click "connect"

    5. Execute a query for:

    USE SUSDB
    GO
    exec spGetObsoleteUpdatesToCleanup

    You will see a list of obsolete updates pop up. Copy these to a txt file.

    6. Execute "exec spDeleteUpdate @localUpdateID=000000" where 000000=UpdateID

    Edit: I wasn't using or maintaining WSUS so there were nearly 20,000 obsolete updates!
    • Edited by techbiker Friday, August 01, 2014 7:00 PM
    Friday, August 01, 2014 6:56 PM
  • So... My take is that the SQL server causes this problem when there is to much data

    It's true that this caused because of "too much data". Why there is too much data still seems to be a matter of heated debate, although I suspect the answers are (as almost always) directly related to these two questions:

    • How many updates do you have synchronized to the WSUS server? - well, we know from your original post that there are "6,000 updates ... awaiting approval" ... which means there's several thousand that ought to be declined but aren't.
    • How many updates are approved? If this number is more than a few hundred, then there's probably several hundred more updates here that need to be declined.

    Aside from that... as has been discussed here almost weekly for the past several years, you will simply need to continue rerunning the Server Cleanup Wizard "Delete unneeded updates.." task until it completes successfully.

    I discussed in another thread a few days ago the relationships between ASP.NET and SQL Server that make this necessary, and what the TIMEOUT errors really mean for your environment.


    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.

    Friday, August 01, 2014 9:40 PM
  • As expected, he arrogantly posts when asked not to...

    Mr. Garvin: You don't get it. You don't read what people write, you just keep spewing the same spam without actually trying to understand or help.  You don't provide answers to what is actually happening, but instead to what you think is happening. 

    It doesn't matter how many times you try to rerun the cleanup wizard if it doesn't make any progress each time you run it. 

    Please just go away? 


    Saturday, August 02, 2014 12:21 AM
  • Thanks techbiker for the more detailed docs on getting logged in there. I should have documented it, but wasn't going to go through that again! LOL.
    Saturday, August 02, 2014 12:23 AM
  • So the cleanup wizard has been running since Friday. No timeouts. It's now made progress up to about 1/4 of the progress bar distance. Memory use is pegged at 15.3 of 16G total physical memory. Almost 7GB used by sqlsvr.exe and sqlsvr.exe *32. Normally I see /much/ less memory use. Like 7 or 8 GB used and the sqlsvr set takes a couple of gigs. 

    But it isn't timing out and it is making (very very slow) progress. 

    Monday, August 04, 2014 6:50 PM
  • Another question RE: this slowness... do you have any locally published/customupdates on this WSUS server? I am looking through the code path and trying to find out where the source of slowness might be.

    If you can reproduce the slow cleanup behavior and could take a SQL trace, that also might help us improve the process in the future.

    Thanks,
    Ben

    Thursday, August 14, 2014 6:54 PM
  • Since this seems to be a bit of a debate, I would like to add my 2c worth of experience with this behavior. A few days ago, we just began to observe the timeouts/node-reset behavior from our core WSUS deployment (several downstream servers, but that is inconsequential at this point.).
    We run the clean-up wizard on a regular basis to keep disk space requirements lean, so the issue for us is definitely not caused by a build-up of updates due to lack of maintenance.

    After a day of reading seemingly repetitious forum discussions about the issue it seems to be a commonality that the observed behavior is being caused by an ASP.NET timeout while waiting for a response from a SQL query.  This seems to be the common resultant behavior, but not necessarily the root cause.  I spent a few hours Friday evening digging into this and actually discovered that in our environment there was one individual update that was causing the problem - (at least for us, your scenario may be slightly different).

    I began by running a SQL trace on the Server clean-up wizard traffic.  It took a little bit to isolate just that traffic, but using a combination of the [database name] and [application name] column filters in the trace it is possible to isolate just the MMC traffic to/from SQL server and then capture the cleanup wizard session.

    From the trace, I identified the SQL queries being executed - (correctly identified above as spDeleteUpdate).  I then read through this SP to further understand what it was doing.  Long story short, turns out that spDeleteUpdate enumerates all *revisions* of an update to be deleted and then calls spDeleteRevision which is what 'actually' does the deleting.  spDeleteRevision then attempts to recurse through the list of identified revisions one at a time (in numerical order) to delete the reference of each revision from about a dozen or so different tables in the WSUS database.
    For each delete statement in the DeleteRevisioins procedure (actually, for each step in both procedures) there is fail-safe coding which will roll-back the ENTIRE delete transaction if an issue is identified at any step in the process.  In our case, the issue was the ASP.NET time-out forcing an abort of the SQL query, which rolled back the transaction.  This is why I saw the Cleanup Wizard repeating an attempted delete on the same updateID every single time I captured the trace.

    So, I then isolated and manually ran each of the identifying steps from DeleteUpdate and DeleteRevision until I found what I believe is the problem.
    The one update that is constantly failing has 883 revisions within our WSUS database.  This is compared to about 10 revisions (give or take) for other updates in the WSUS database - (tested by counting the revisions of a random sampling of updates).
    (The update in question is identified as an Office 2013 update.)
    Knowing that for each one of these 883 revisions, the spDeleteRevisions procedure performs a dozen or so deletes, each step having fail-safe/double-check code which is capable of rolling back the entire transaction, I believe it is entirely possible that the cleanup wizard simply cannot get past this specific update because it is not designed to allow enough time for the procedure to recurse through such a massive list of revisions.

    When I examine the SQL trace, I can see and follow the logical steps generated by spDeleteUpdate and into the spDeleteRevisions code.  So, I know that the wizard and stored procedures are performing their duties properly, it seems that there are simply too many revisions that have to be recursed through within the pre-defined time-out period.  Coupled with the fact that the delete procedure will roll-back the entire transaction should *any* issue be identified, the problem ends up being the sheer volume of revisions.

    So, not to beat the dead horse, but I would have to agree with the original poster James Newton in such that if you can run a SQL trace and identify a single update as being the cause of this issue, then run the spDeleteUpdate manually in Management Studio and allow it as much time as it needs to run through to completion.  (Stop the Update Services - Windows service and backup your database first.)
    I'll be further verifying this during my next maintenance window and advise on results as they become available...

    During initial troubleshooting, I tried changing the time-out setting at the web-node level for the WSUS web-site, but that didn't make a difference.
    Alternatively, I thought of perhaps trying to change the timeout settings for ASP.NET to allow the cleanup wizard enough time to actually complete this monster delete, but I'm not sure if that time-out setting is something that would be ASP.NET configuration specific, or if the time-out is built into the cleanup wizard's code.  Being less intrinsically familiar with ASP.NET, I'm more comfortable fixing the problem manually at the SQL level.
    If someone else knows if/how this can be resolved by temporarily editing the ASP.NET time-out configuration, please feel free to validate or refute this theory.

       --UPDATE:  Removing the offending update by way of running the spDeleteUpdate procedure from SQL Management Studio did indeed fix the problem in our environment.  Cleanup Wizard ran successfully with all options checked immediately thereafter.  No adverse affects observed so far...

                Cheers!


    The solution is always the last thing you look at... -M



    Monday, August 18, 2014 3:28 PM
  • Another question RE: this slowness... do you have any locally published/customupdates on this WSUS server? I am looking through the code path and trying to find out where the source of slowness might be.

    When you say "locally published/custom" do you mean like I wrote a program and added it to WSUS to push to the local workstations? No, nothing like that. Just a standard little SBS setup.

    Tuesday, August 19, 2014 6:49 PM
  • Just as an update to my own situation. The cleanup wizard finially finished after days of running it again and again. It was very, very slow, but each time it made a little progress, which hadn't been the case before doing the manual work in SQL.

    Now I've downloaded the command line cleaner from

    and setup a task for once a week with:

    WSUS_Cleanup_CL.exe myserver f 8530 all

    where myserver (obviously) is the name of my server. And it generates this:

    Running with the following parameters:
    Server Name: myserver
    Using SSL: f
    Using Port: 8530
    Stopping the wsusservice
    Ok, stopped
    Working. This may take a while. An event will be entered
    in the Application Event log when the utility completes.
    The wsusservice is now Running
    The maintenance script (DB_maint.sql) was not found.  Make sure the sql file (DB
    _maint.sql) is in the same directory as WSUS_Cleanup_CL.exe if you want the SQL
    maintenance script to run.
    Done.  Please check the Application Event log for the cleanup results.

    I'm not sure if I'm going to include the sql script.  The Application log entry looks like this:

    Log Name:      Application
    Source:        WSUS Cleanup
    Date:          8/19/2014 12:56:56 PM
    Event ID:      996
    Task Category: None
    Level:         Information
    Keywords:      Classic
    User:          N/A
    Computer:      my server
    Description:
    Cleanup Results:
    265070901byte(s) removed from the WSUS Content directory.
    0 expired update(s) were declined in WSUS.
    0 computer(s) were removed from WSUS.
    256 obsolete update(s) were removed from WSUS.
    0 superseded update(s) were declined in WSUS.
    7258 Unneeded revision(s) were removed from WSUS.



    Tuesday, August 19, 2014 8:46 PM
  • 265070901byte(s) removed from the WSUS Content directory.
    7258 Unneeded revision(s) were removed from WSUS.
    Interesting.

    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 20, 2014 1:04 AM
  • 265070901byte(s) removed from the WSUS Content directory.
    7258 Unneeded revision(s) were removed from WSUS.
    Interesting.

    Lawrence Garvin, <snip tons of self serving, ego bosting, unnecessary junk>

    I would ask what you think is so "interesting" about that in light of the fact that we have REPEATEDLY stipulated that, yes, we know; the problem is caused by not running the cleanup wizard regularly, or constantly declinding and removing updates manually, which we didn't know we needed to do and which WSUS should have been doing automatically in the first place... but then your reply would only put another copy of your massive, self serving, ego bosting, unnecessary signature block into this thread and waste everyones time. Please, for the love of god go away and let us actually solve this problem?


    Instead, I will provide some USEFULL commentary by posting what came from the second run of the command line cleanup wizard from:
    http://wsus.codeplex.com/releases/view/17612

    Log Name: Application Source: WSUS Cleanup Date: 8/20/2014 10:04:44 AM Event ID: 996 Task Category: None Level: Information Keywords: Classic User: N/A Computer: myserver Description: Cleanup Results: 0byte(s) removed from the WSUS Content directory. 0 expired update(s) were declined in WSUS. 0 computer(s) were removed from WSUS. 0 obsolete update(s) were removed from WSUS. 0 superseded update(s) were declined in WSUS. 465 Unneeded revision(s) were removed from WSUS.

    This ran one day after the first, and STILL found 465 revisions that somehow were either not removed the first time, or were reloaded between yesterday and today. I ran it again minutes later and it found 0 unneeded revisions to remove. I'll be curious... no, it will ACTUALLY be "interesting" to see what it comes up with next week.

    On another subject: When creating the Task for WSUS Cleanup Wizard in Task Scheduler, remember to click "Run with highest privileges". The wizard needs to be Admin to connect to the server.

    On another subject, I had placed the .sql script that comes with the command line cleanup wizard into the same folder but when I run the script I get:

    Running the database maintenance script now...
    Timeout expired.  The timeout period elapsed prior to completion of the operatio
    n or the server is not responding.
    so for now I'm just removing it. I don't think it should be necessary as long as the main part of the wizard continues to remove unnecessary entries. The interesting thing is that it prints that immediatly, without any delay or what I would expect as a timeout. Am I missing a bet there?
    Wednesday, August 20, 2014 5:36 PM
  • Guys, don't know why this is not mentioned:

    Extend Timeout (f.e. for Cleanup-Wizard)
    Connect to SQL via: \\.\pipe\mssql$microsoft##ssee\sql\query
    Right click on \\.\pipe\mssql$microsoft##ssee\sql\query (first in the row on the left)
    Select Properties
    Select Connections
    Set timeout from default 600 to 0 (= no timeout)

    Regards
    • Edited by John Stere Thursday, August 28, 2014 10:30 AM
    Thursday, August 28, 2014 10:28 AM
  • Extend Timeout (f.e. for Cleanup-Wizard)
    Connect to SQL via: \\.\pipe\mssql$microsoft##ssee\sql\query
    Right click on \\.\pipe\mssql$microsoft##ssee\sql\query (first in the row on the left)
    Select Properties
    Select Connections
    Set timeout from default 600 to 0 (= no timeout)

    Ah, John, excellent! Why didn't I think of that?

    On the other hand, I'm not sure I would set it to 0... the idea that a script could be allowed to run forever is a bit scary... but I assume we could also just double it to 1200 or 2400?

    Thursday, August 28, 2014 3:45 PM
  • Over the years I've inherited many SBS2011 boxes that were never maintained properly (never mind WSUS specifically!) I've tried staggering the clean-up steps, the SQL maintenance script, playing with timeout values, nothing ever really helped except persistence and when all else failed re-creating the SUSDB via http://technet.microsoft.com/en-us/library/gg680316.aspx which is a pain.

    I inherited yet another last week and kept experiencing timeouts despite everything. Found the comments at http://wsus.codeplex.com/releases/view/17612 very useful (which led me to finding this thread when I googled spGetObsoleteUpdatesToCleanup and spDeleteUpdate)

    I took a backup of the DB before doing anything and then ran...

    USE SUSDB
    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 
    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
    

    And touch wood everything seems absolutely fine. I'm sure that like JustUnplugIt there's probably 1 or 2 updates that are causing the problem due to the number of revisions and removing those manually will then allow the Cleanup Wizard to complete as normal but I just ran the script instead as that at least allowed me to keep an eye on what it was doing rather than relying on a progress bar with no other information!

    Thanks to all involved, have saved me a few hours faffing with the WSUS re-installation procedure.

    Friday, August 29, 2014 11:23 AM
  • Thanks to everybody contributing to this thread, it gave me some much needed hope!

    Now I'm just a bit less stuck than before and I'd be very grateful if somebody could point me a bit further in the right direction. We're running an old SBS2008 box that has previously served some 50 clients, mainly XP. Now most of them are retired but the server is still in use for Exchange, ftp and updates for a few newer windows clients. It has been standing pretty much unmanaged for at least four years and I just recently started to dig into the task of bringing it up to date.

    The WSUS database contains a little over 40000 updates and like many others I had no luck with the cleanup wizard. The maintenance script made no difference and now I'm poking around in SQL Management Studio Express trying to get the suggested solutions to work.

    At first I ran "EXEC spGetObsoleteUpdatesToCleanup" manually, it returned some 36000 rows and I continued with "EXEC spDeleteUpdate" manually a few times just to see if it would work and it did. Next step was the script posted by vexation, I added an IF-clause inside the while loop to limit the deletions to IDs lower than 200000 like so:

    ...IF @var1 < 200000
     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...
    It has so far run for six days without a hint of anything happening. In the activity monitor the "cpu" column is steadily adding up, but no progress is made in any of the other columns. Physical io is stuck on 4092 since day one and "Last batch" also happened the first day.


    How would I find out if there's any point in letting the query continue?

    When trying to get the properties of the WSUS database I get "Property Owner is not available for Database '[SUSDB]'" and indeed, "sp_helpdb SUSDB" lists the owner as NULL.

    Is this normal or should I assign an owner to the database? In that case, which one is default, the "dbo"-user? Could the lack of an owner have anything to do with the inability to clean the database successfully?

    Kind regards
    Alexander

    Monday, September 08, 2014 3:32 PM
  • How would I find out if there's any point in letting the query continue?
                     I used the SQL profiler to watch the progress of the cleanup scripts.  If you isolate the traffic to/from the WSUS database (SUSDB) and have the profiler watch for batch-start and query-start, batch-end and query-end you should see the movement.  If you are using the WSUS cleanup wizard for this process you can also enable tcp-start and tcp-end to watch for the cleanup wizard sending the commands to sql to begin the process and capturing the results.  If you are performing cleanup manually in SQL Management Studio then you don't need the tcp-start and tcp-end events.
                    I would also suggest to maybe step through the process manually in Management Studio for one update to see if SQL finishes the cleanup work-flow. If you start a 40000 update cleanup I'm not surprised if it takes a while.  Start by isolating one update using [spGetObsoleteUpdatesToCleanup].  This will provide a list of obsoleted updates LocalID - (Take the first/lowest number).  Then run [spDeleteUpdate] @UpdateID (if UpdateID were 1234566 you would type into management studio (exec spdeleteupdate 1234566).  If that finishes successfully, you at least know the update cleanup workflow is successful.  Check spGetObsoleteUpdatesToCleanup again for the updateID that you just deleted...

                     Additionally, if you have the cleanup process running and it is taking a long time, you should be able to open a new query to the SUSDB database and run the 'spGetObsoleteUpdatesToCleanup' script by itself and count/review the list of updates that are returned.  If the cleanup process is running on a very large set of obsolete updates, the list returned by this query should be shorter every time you re-run the query.
                     I would run that GetObsoleteUpdates script before beginning cleanup to get a baseline.  Since the cleanup process uses a SQL Transaction to perform the cleanup steps and only commits the transaction once the entire set of steps is complete for each update being deleted;  If the GetObosoleteUpdatesToCleanup script runs for a long time without returning anything (it should only take a few seconds), then the cleanup process is probably preventing it from completing due to an open transaction that would have an affect on the outcome of the get query.

    Is this normal or should I assign an owner to the database? In that case, which one is default, the "dbo"-user? Could the lack of an owner have anything to do with the inability to clean the database successfully?
                       In double-checking our SQL, our SUSDB is owned by the sa user.  I don't really think that ownership has anything to do with whether or not the cleanup works.  In my observations, I haven't seen anything related to the cleanup process being executed by the SQL db owner, usually the cleanup wizard executes with SQL user permissions of the computer account that is running the 'Update Services' windows service (assuming you have the database on a separate SQL server from the WSUS service).
                       If SQL is running on the same box, (as it may be in an SBS deployment) then it may run under the context of whatever user is assigned to the logon settings for the  'Update Services' windows service.  You may have to make sure that the logon account that is running 'Update Services' has enough permissions in SQL.
                        There are also registry entries for the Update Services windows service - for a SQL username/password (hashed), although I've never had to use them.   (HKLM\Software\Microsoft\Update Services\Server\Setup --> SQLUserName and SQLEncryptedPassword)

    I would think that if you were having permissions issues that you would have bigger problems such as inability to synchronize/download updates, inability for clients to download/execute updates, inability of clients to report back statistics.  If WSUS is working aside from cleanup, I doubt you're having permissions issues.


    The solution is always the last thing you look at... -M


    Monday, September 08, 2014 4:31 PM
  • So you're keeping track of the update delete in progress using this code:

    SET @msg = 'Deleting ' + CONVERT(varchar(10), @var1) RAISERROR(@msg,0,1) WITH NOWAIT

    Maybe you're stuck on one particular update? I don't think it has to do with the db owner.

    Regards,
    Ben

    Monday, September 08, 2014 4:52 PM
  • Thanks JustUnplugIt and Ben! I'll look into the SQL-profiler and see if I can make something out of it.

    I would also suggest to maybe step through the process manually in Management Studio for one update to see if SQL finishes the cleanup work-flow. If you start a 40000 update cleanup I'm not surprised if it takes a while.  Start by isolating one update using [spGetObsoleteUpdatesToCleanup].  This will provide a list of obsoleted updates LocalID - (Take the first/lowest number).  Then run [spDeleteUpdate] @UpdateID (if UpdateID were 1234566 you would type into management studio (exec spdeleteupdate 1234566).  If that finishes successfully, you at least know the update cleanup workflow is successful.  Check spGetObsoleteUpdatesToCleanup again for the updateID that you just deleted...

    Sorry if I was unclear but this was tried successfully before starting the looped cleanup.

    Additionally, if you have the cleanup process running and it is taking a long time, you should be able to open a new query to the SUSDB database and run the 'spGetObsoleteUpdatesToCleanup' script by itself and count/review the list of updates that are returned.  If the cleanup process is running on a very large set of obsolete updates, the list returned by this query should be shorter every time you re-run the query.

    I did this, unfortunately I did not note the value of 'spGetObsoleteUpdatesToCleanup' the first time i ran it but the message returned from the looped cleanup is '36095 row(s) affected' and it was returned at initial execution, yesterday 'spGetObsoleteUpdatesToCleanup' returned 36237 rows and today 36254 so it's actually increasing(!?)

    SET @msg = 'Deleting ' + CONVERT(varchar(10), @var1) RAISERROR(@msg,0,1) WITH NOWAIT

    I've not gotten any message since the '36095 row(s) affected' so I guess it's looking kind of dark. Not happy about canceling the whole thing either, risking a lenghty rollback.

    Thanks again so far!

    Tuesday, September 09, 2014 10:05 AM
  • MS is still pushing out updates, so depending on how long you've been letting this cleanup process run so far, new synchronizations from Windows Update may still be identifying new updates from Microsoft.  Which, in theory, if any of those new updates revise or supersede current updates and cause them to become obsoleted the UpdatesToCleanup count could very well be growing.

    How are you running the cleanup process currently?  Using the WSUS GUI Wizard or are you doing this directly from SQL?  It seems like you've at least tested the workflow in SQL directly.  How long has it been running uninterrupted?


    The solution is always the last thing you look at... -M

    Tuesday, September 09, 2014 12:59 PM
  • Yes, that was my initial thought too but before I started mucking about in SQL express I disabled the Update services and did not expect any external alteration of the database. Maybe an erroneous assumption from my side? I did decline a bunch of updates early in the "investigation" through the WSUS MMC and these might just have passed the 30-day limit mentioned in the Cleanup Wizard.

    The current cleaning process is done in SQL, I tried the MMC Cleanup wizard (the topmost "big" cleanup) after doing a few manual SQL deletions but it timed out so fast that I did not bother trying more than a handful times. The SQL script has been running uninterrupted for seven days and four hours...
    Tuesday, September 09, 2014 1:20 PM
  • Thanks again JustUnPlug it for pointing me to use the SQL Profiler, or rather, the SQL Trace functions since I only had the Express version of management studio available. I've now got the Cleanup Wizard running and the progress bar is moving!

    I really don't know what the exact solution was though, the SQL query I was worried about ended immediately when I hit stop so I don't think it did anything at all. But I did restart the internal database from SQL Server Configuration and that might have been it.

    One beverage of your favorite kind will be on me if we'd ever meet!

    15 hours 47 minutes ago
  • I think the key is just doing a few of the spDeleteUpdate's to get it past whatever is jamming it up, and then it will start working on it's own again. If it stops, just spDeleteUpdate manually (as per the post at the top) and then try the wizard.
    3 hours 20 minutes ago