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.

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.

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

• Edited by techbiker Friday, August 01, 2014 7:00 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

• Edited by techbiker Friday, August 01, 2014 7:00 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.

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? 

• Edited by James Newton Monday, August 04, 2014 6:44 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? 

• Edited by James Newton Monday, August 04, 2014 6:44 PM

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. 

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

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...

            C

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.

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.

265070901byte(s) removed from the WSUS Content directory.
7258 Unneeded revision(s) were removed from WSUS.

265070901byte(s) removed from the WSUS Content directory.
7258 Unneeded revision(s) were removed from WSUS.

In

• Edited by John Stere Thursday, August 28, 2014 10:30 AM

• Edited by John Stere Thursday, August 28, 2014 10:30 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?

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.

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...

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

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.

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

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!

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?

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!

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.

Rocking! James, you made my day - got a new customer and unlike all of our regular customers  this SBS 2011 box was completely unserviced. So was the WSUS and the "delete unused..." ran into the never ending crash.

With your solution and the script from vexation we got it solved.

Thanks from Germany, Jrg 

• Edited by old york Tuesday, September 30, 2014 9:33 AM

Rocking! James, you made my day - got a new customer and unlike all of our regular customers  this SBS 2011 box was completely unserviced. So was the WSUS and the "delete unused..." ran into the never ending crash.

With your solution and the script from vexation we got it solved.

Thanks from Germany, Jrg 

• Edited by old york Tuesday, September 30, 2014 9:33 AM

In my situation I manually deleted the first three updates listed at the top of the list as well as 3 at the very bottom after running the query. I am now running WSUS cleanup again and while it is a long slow process, it has been running now for several hours without any time outs or disconnects.

Thanks James, well done.

Thanks, these are more or less the steps I used, just keeping in mind the server name needed to be "\\.\pipe\MICROSOFT##WID\tsql\query" in my case (Windows server 2012 R2) and I also needed to open the SQL Server Management Studio with the "run as Administrator" option in order for built in authentication to work.

I deleted a few updates from the top of the list and a few from the bottom - WSUS cleanup wizard seems to be working great now with no timeouts. *fingers crossed*

Using an elevated command prompt was the only way I could get this to work on SBS 2008.  Of course the server I'm working on was setup by some kind of drunken monkey or something since they somehow managed to screw EVERYTHING up on it.  Not sure how but WSUS wasn't even connecting to any servers but was building quite the big database.  Of course the cleanup was NEVER run either.  Still can't connect using SQL management, BUT opening up an elevated command prompt and running the following worked for me on a seriously messed up WSUS with a TON of disk space being eaten up!

sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

USE SUSDB
GO
exec spGetObsoleteUpdatesToCleanup
GO

exec spDeleteUpdate @localUpdateID=000000
 where 000000 is the ID

Fun fact for the da - you can copy and paste that code from here to a command prompt!  If you copy the blank line trailing a command you can be extra lazy and not even have to hit the Enter button!!!

This worked for me as well. Deleted a few from the top and a few from the bottom, total about 20. Launched the cleanup wizard again and no timeouts. It was running for an hour and I launched a new query while the wizard is running and can see the list is shorter by about 91. I had over 8600 so this is going to take a while.

Thanks

Good results here as well.  I connected via a regular TCP connection but temporarily set the timeout for remote queries to 0 (infinity).  The first thing I did was reindex all the tables.  Other discussions suggest that reindexing doesn't fix the issue (and it didn't for me either), but I also figured it could do nothing but help.  In the end it didn't end up taking long for the first update to be deleted--about three minutes--but WSUS appears to use some custom timeout shorter than that I guess.  I could have left the remote query timeout at its default of 600 seconds.

Short version:  List obsolete updates and manually delete a few of them.  Try the wizard again when the deletes start taking reasonable amounts of time again (ie seconds).

-- List obsolete updates
exec spGetObsoleteUpdatesToCleanup;

-- Delete an obsolete update
exec spDeleteUpdate @localUpdateID=#####;

• Edited by AMD Avenger Thursday, January 22, 2015 6:08 PM

Good results here as well.  I connected via a regular TCP connection but temporarily set the timeout for remote queries to 0 (infinity).  The first thing I did was reindex all the tables.  Other discussions suggest that reindexing doesn't fix the issue (and it didn't for me either), but I also figured it could do nothing but help.  In the end it didn't end up taking long for the first update to be deleted--about three minutes--but WSUS appears to use some custom timeout shorter than that I guess.  I could have left the remote query timeout at its default of 600 seconds.

Short version:  List obsolete updates and manually delete a few of them.  Try the wizard again when the deletes start taking reasonable amounts of time again (ie seconds).

-- List obsolete updates
exec spGetObsoleteUpdatesToCleanup;

-- Delete an obsolete update
exec spDeleteUpdate @localUpdateID=#####;

• Edited by AMD Avenger Thursday, January 22, 2015 6:08 PM

Thanks James, and others, for your posts here. I think I'll finally be able to get our database cleaned up.

I did like others have done and ran the command to get the list of everything that needs deleted and then randomly deleted a few one by one to make sure it was going to work. Then when I hit it with Jame's script it took longer to delete the first update than when I did them manually so I stopped the script and tried to delete it manually. It's been running for over three days. Do I wait it out or is there something else I can do? I am *completely* SQL illeterate so dumb it down for me :-)

Thanks,

Randy

I'm just deleting one update.

First of all thanks for the help.  I was stuck on Cleanup wizard too.  33Gb database.

EXEC spGetObsoleteUpdatesToCleanup returned 34 491 rows

Now, I've started the script above and it's been deleting updates for 19 hours.  It's not fast. Running the EXEC spGetObsoleteUpdatesToCleanup again , I get 33971 rows, so if I get that correctly, 19 hours of deletion got me about a thousand rows removed.

What were your results ?

Yeah, that's about right. Others have reported it taking more than a week. Mine was less than 10,000 to clean up and it took a few days of running it again and again. TwinOak reported having to stop it and re-start it or manually delete one or two before it would run all the way on it's own, so if you find it makes no further progress, you might interrupt it and restart it or manually delete one to get it going again.

The key point is that once you get the junk cleared out, then the regular cleanup will run "quickly".

Do you think Microsoft should have made this an automatic task, that comes pre-scheduled with a new install of the OS? Or should they have provided a better "checklist" of things a server herder should be doing? Or is this acceptable as it is?

Hey, guess what else Microsoft isn't doing and didn't tell us? In Windows 2008, the scheduled defrag is disabled by default!

https://social.technet.microsoft.com/Forums/scriptcenter/en-US/7b37eb42-b617-4fe7-9e04-977e9beb60f2/windows-2008-r2-sp1-scheduled-task-defrag

And, this is just hilarious, there IS a task set up for it in Task Scheduler! Why setup a task and then not enable it?

I just defrag'd my C drive for the first time after several years of operation. And now I'm defragging my data drives. It's been running for 2 days... 

Any other issues you guys know about that we should be doing and don't know to do?

Yeah, that's about right. Others have reported it taking more than a week. Mine was less than 10,000 to clean up and it took a few days of running it again and again. TwinOak reported having to stop it and re-start it or manually delete one or two before it would run all the way on it's own, so if you find it makes no further progress, you might interrupt it and restart it or manually delete one to get it going again.

The key point is that once you get the junk cleared out, then the regular cleanup will run "quickly".

Do you think Microsoft should have made this an automatic task, that comes pre-scheduled with a new install of the OS? Or should they have provided a better "checklist" of things a server herder should be doing? Or is this acceptable as it is?

Yes I certainly think it should have been pre-configured from the get go.  This is a Small Business Server after all, by definitions, it will be installed in small enterprises with alot less IT resources, so it should.  We can see that trend with all the included wizards you don't get on Standard and up versions.

I had configured SUSDB to be on a separate partition than the boot one ( by default ) so by chance I wasn't struck with a nearly full C:.  I had limited the memory SQL was allowed to use on the SBS server as I found it was a hog, but never in my dreams I would have imagined they would have packaged WSUS with SBS, no strings attached.  35Gb database ? At least you should get a warning, something.  Maybe it's my fault not monitoring WSUS on a daily basis...I also have to take care of VMware vSphere, Linux servers, storage, SAN, networking and everything else on top of business growth on the IT side which brings me to my original point and your question, on a SBS product, it should be at least managed by pre-configured cleaning tasks or alerts....monitoring, managing and taking care of a WSUS database daily or weekly should not be a "concern".

I copy pasted the list from SQL Studio of the spGetObsoleteUpdates into EXCEL added a column in front and placed the code before the LocalUpdateID and added a column after with "

COPY pasted the whole mess into NOTEPAD

Replaced all the TABS with blanks

Saved it to a batch file and let it run at an elevated prompt for a very long time.

spDelEm.bat

sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -Q "EXEC SUSDB.dbo.spDeleteUpdate 214743"
sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -Q "EXEC SUSDB.dbo.spDeleteUpdate 214718"
sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -Q "EXEC SUSDB.dbo.spDeleteUpdate 214706"

... about 2,000 more of these in between

After I cleared them even my most stubborn of servers could run a WSUS Server Cleanup Wizard.

So, can you guys tell me if you were able to shrink the database size by doing all this ?

Because one of the reasons I was looking into this was to bring back my 30+Gb WSUS database to a more normal size.

As far as I can tell (after having run the same WSUS implementation for almost 10 years) - the database will continue to grow as long as it is accumulating update metadata.  This inherently includes updates for products that are retired (Win 2k, Win XP) as there is no way to 'remove update metadata' from the database once those products are retired and the updates are no longer relevant.

The WSUS cleanup wizard only declines superseded updates but does not remove the metadata from the database, even if you remove/uncheck the product in the products and classifications window.  Roughly the same behavior as it takes for the actual update data files - it will delete superseded update files but the WSUSCONTENT directory seems to continuously grow, no matter how well you clean it up - and there is no inherent way to purge updates for de-selected products without jumping through hoops**.

In short, WSUS database seems to keep historical update data whether you like it or not.  I've never been able to remove obsolete update metadata.  The only thing I can think of would be to delete and recreate the WSUS database with only currently valid products and classifications selected for synchronization.

** Regarding purging update files:  I have stumbled upon a relatively easy way to purge the truily unneeded update data files for de-selected products.  I can't take credit for it, I found it mentioned in another blog somewhere that I can't remember at the moment.

  In the 'Update Files and Languages' window in the options section of WSUS console, set the update storage to 'Clients download directly from Microsoft' - In my environment, the WSUS servers chewed on this change for about an hour, then when the 'Update Files and Languages' window was no longer showing me that the server was still processing a previous change, I ran the cleanup wizard which completely emptied the WSUSCONTENT folder with the exception of eula files which are all small text files - a few Kb each.

After running the cleanup wizard, re-set the 'Update Files and Languages' setting to store updates on the server - BE SURE TO SELECT ONLY RELEVANT LANGUAGES on the languages tab or you'll blow up your WSUSCONTENT directory.  After about another hour of chewing on that change again the WSUS server began to download update content files again using BITS, but the content directory was 1/10th the size of where it started.  (Note:  In my situation we were retiring XP updates which took up probably close to 75GB or more of space.  YMMV with regards to how much space you'll save, it depends on the products you still have selected vs. what will be purged.)

As for reducing database size, I would say delete and re-seed the WSUS database from it's most recent installation package.  That would be the only sure-fire way to remove irrelevant data.

I'm already planning on rebuilding our entire WSUS infrastructure for the same reason, database size and relevant data retention.  After almost 10 years I think the WSUS database could very well do with a good press of the reset but

Thanks !

Yeah I have stumbled on that blog today regarding the procedure you are talking about.  

For the moment, to make sure my domain computers were receiving updates, I bypassed WSUS using a GPO and set/force the installation schedule on weekend during the night.

As for deleting and re-seeding the WSUS database, how would you proceed ?  Just unmount, delete and ? I'm not too sure about re-seeding the DB.

Well, first of all - if you set the 'Files and Languages' setting to have clients download from Microsoft directly rather than your WSUS server, the clients will still look to your local WSUS for which updates are approved and also report back installation results to your WSUS server so you don't have any gaps in reporting, but they will download the actual update data files direct from Microsoft rather than your local WSUS repository.  It is not necessary to change your GPO settings to point the clients back to MS Update in total.  The only impact of the 'Files and Languages' settings change is WAN bandwidth consumption as multiple clients all download the same files from MS Update which is going to happen anyway if you have them look to MS Update in total.  If you have your update settings scheduling Automatic Update to only run during off-peak hours, that shouldn't be too much of an issue.

As for rebuilding the database, there are 2 ways you can go about it.  You can backup the database & IIS web-site and then uninstall WSUS completely, download the latest WSUS install package from MS (only about 86MB) and re-install from scratch which will recreate the database and the web-site.  You might also be able to download the same installation package, un-compress it to a temp folder and extract the seed database file, or find the schema scripts which will build the WSUS DB schema out of a blank 'NEW' database which you can manually create using Management studio.  If the installation package does not have a seed database .mdf file that you can easily mount, the other method (build schema using scripts) might be problematic if you are unsure of how to go about it or whether or not you have all necessary scripts to correctly create the schema.  I would probably recommend uninstall/reinstall.  At that point (after uninstall) you can also manually empty out the WSUSCONTENT folder and have the new installation repopulate the content with only relevant update data files.  You will probably also see a great reduction in storage requirements after a rebuild, assuming you have not selected all of the same products you had previously selected (ie. drop WinXP, e

Thank you for the reply.

I would go for the uninstall / reinstall route as I would feel more comfortable.

I assume reinstalling WSUS on SBS 2008 is possible.  

I know that SBS is a little bit of a different animal with regards to these baked in features, but it should be pretty straight forward.
Google found an article:
http://www.channelpronetwork.com/article/reinstalling_wsus_on_sbs_2008_and_2011_too
I don't have an SBS environemnt to test with, however I read through it quickly and it seems correct based on what I know about WSUS.  I doubt you'll have any problems with the uninstall as it doesn't seem like you have any functional issues with WSUS, only an exorbenant amount of data that you're trying to purge.

As far as the reinstall is concerned, I would recommend the downloadable installation package option as opposed to media as it may be a more up-to-date version than what might be on a disk.

So, before you start in on a project that may become a big can of worms, let's have a quick look at why you want to do this:
30 Gb is a pretty big WSUS database.  How long has this instance been running?  Mine are 1-3 GB and have been running for almost 10 years.  It almost seems like you might have either too many products selected, too many languages selected or you've also selected to have WSUS update tools and drivers from the categories list, most of which probably won't apply to a normalized environment.  As well, drivers should be vetted and tested before deploying; I wouldn't necessarily recommend deploying drivers through WSUS.

If this is the case, than reinitializing the database may not solve your problem.  It will quickly re-grow to the same size it was if you reinstall it with unnecessary options selected.  Before you go through all the trouble, examine whether or not you have these items selected and actually require to have them staged within your WSUS environment.  If you have every update, tool and driver in every language than I wouldn't be surprised you have a giant WSUS environment.  If you DO find that you have too many products, categories and/or languages selected, than leaning them out won't necessarily purge the data and you may still want to re-initialize.  Well, perhaps the languages, but I based on my experience, deselecting products, etc. doesn't necessarily dump all of that in formation form the database.

I have a 30Gb database and the WSUS Content folder is 62Gb.  I'll be honest, I never used WSUS , neither really maintained it.  This server has been installed in 2010.  

As it is, I would say this is a stock installation from SBS, I never added any product or language.  From what I've gathered in this thread, it might be possible SBS has some issue where it selects too many items as many people are reporting huge databases and or WSUS content folders.

Again I thank you for your help, before going in I will investigate what are the items selected, I would only want security updates for Win7x64 now.

Read you.

Oh I have a few other servers, mainly Windows 2003 R2 ( phasing it out ) and a few Windows 2012.  Others are Linux.

SBS do come with MS SQL and Exchange 2007.  
We use office, so yeah definitely on this one.

Firstly, I would really like to thank and give a ton of credit to James Newton/vexation/JustUnplugIt

SHORT VERSION
This helped a ton guys.  Thank you so much for your work/input on this.

LONG VERSION
JAMES, you were right on with pursuing this and I had/have the exact issues you originally started the thread about.  I was not properly maintaining my WSUS databases as I should have.  Honestly, I didn't know I needed to as I presumed that was built-in..big mistake.  This thread has helped me a ton with a very troublesome SBS2011 environment and I think the WSUS situation here may be about half the problem.  I'm still in the process of getting it all cleaned up but this is gold in terms of practical/applicable help so thank you.

VEXATION, I actually created a sql script I was trying to come back to this thread with to share to help the community, etc.  However, your script is so much better.  Yours is small, fully complete, and does it all.  Mine was larger and required a list of WSUS Update numbers be provided to work through.  Yours does the search inline and works from that...very nice..very elegent..small..just great so thank you.

I hope you don't mind but I made just a couple very small changes to your script only to help the output of what people see since this script may take a very long time to run and I thought it would be helpful to know exactly where you're at in the whole list of things.  
That being said, my minor changes only changes the output
from: Deleting 698317
to:   10/19479: Deleting 698317 Apr 27 2015  4:04PM

It's minor but if this is running for hours(very easy for that to be the case) or days(possible in some situations), this little bit of extra info could be very helpful in my opinion...including see how long any particular update took to complete.

Again, I take no credit here as I appreciate the great work and diligence from James Newton/vexation/JustUnplugIt as they drove this issue to a helpful set of steps and also clear reasons as to why. (honorable mention to Techbiker because that's the first summation and useful set of steps that worked well).

Thanks guys and I hope to see you again on future blog topics.  I can also provide an update after all my processes finish running if you would like.

Take care

--************** vexation code with my small changes
USE SUSDB
DECLARE @var1 INT, @curitem INT, @totaltodelete INT
DECLARE @msg nvarchar(200)
CREATE TABLE #results (Col1 INT) INSERT INTO #results(Col1)
EXEC spGetObsoleteUpdatesToCleanup
SET @totaltodelete = (SELECT COUNT(*) FROM #results)
SELECT @curitem=1
DECLARE WC Cursor FOR SELECT Col1 FROM #results
OPEN WC
FETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1)
BEGIN SET @msg = cast(@curitem as varchar(5)) + '/' + cast(@totaltodelete as varchar(5)) + ': Deleting ' + CONVERT(varchar(10), @var1) + ' ' + cast(getdate() as varchar(30))
RAISERROR(@msg,0,1) WITH NOWAIT
EXEC spDeleteUpdate @localUpdateID=@var1
SET @curitem = @curitem +1
FETCH NEXT FROM WC INTO @var1
END
CLOSE WC
DEALLOCATE WC
DROP TABLE #results

Thanks for this both James and techbiker - I used both of your posts to get this sorted. As we are running native WSUS I simply opened management studio, right clicked the SUSDB db and did a new query (pipe setting was evidentally not needed for me, although I may have set this up previously, not sure).

I then actually only had to delete the first 3 updates on mine - the first took about 5 mins to go, the next two about 30 seconds each. The Wizard then started running without crashing and looks like it'll have everything up after 3-4 hours at it's current progress.

I must admit I was very dubious about the idea of deleting "the first few updates", as the updates are listed in reverse numerical order, so it seemed unlikely in some ways that these first updates listed were the ones it was actually getting stuck on ... but I guess the wizard must be accessing them in this order as well, because lo and behold deleting these few did the trick in unblocking it. :)

Many thanks!

I just started a new contract and my first task was getting WSUS cleaned up and up-to-date with applying patches.  1 G of RAM on the server slows things down just a bit.  5 G config database and a 217 G update db.

I'm getting the exact same symptoms and running the listing script showed 3100+ IDs.  After removing about a dozen of them the GUI only reported 4 less.  None of them took over 30 seconds to delete.  We're also going to add more RAM to the server to help alleviate the problems overall with the time it takes to run reports and such.

Thanks for all the info.  I've been in many discussions and IT seems to be the only group that as soon as we find out a solution, we spread it like wildfire and not trademark/copywrite it and make a buck.

Thank you for that script! I didn't think about a MSG output to monitor progress like that. Yours is SO much better than watching "Executing query..." or a progress bar that apparently doesn't move with each update deleted! Fantastic!