Cleaning a Stuck WSUS Server: Too Many Unapproved Updates
This was a tough one. We have labs full of Windows 7 computers that get their updates via a WSUS server. These machines were not getting their updates. Many of them were failing with WSUS error 0x80244010 . (There were some other errors as well, which I foolishly did not write down.)
Error 0x80244010 means "the client tried to contact the WSUS server for more than 200 round trips". http://blogs.technet.com/b/sus/archive/2008/09/18/wsus-clients-fail-with-warning-syncserverupdatesinternal-failed-0x80244010.aspx . "200" is a hardcoded value. Re-running "Check for Updates" on the client several times (I have had to run it three or four times) can fix the issue well enough that the computer gets updates, but this is irritating.
The underlying problem appears to be twofold:
- Every update on WSUS (even if they are unapproved) get their metadata sent to clients unless the updates are marked as expired.
- WSUS does not clean unneeded (obsolete? superseded? unapproved?) updates itself. I had been running it as a "fire and forget" server: allow it to autoapprove security updates and then leaving it alone. This fills the database with a bunch of updates that gets sent out.
WSUS has a Cleanup Wizard which is supposed to expire obsolete and unneeded updates. I tried running this, but it would get stuck and hang forever.
There is lots of advice about how to fix this problem: defragging hard drives, running the Cleanup Wizard multiple times, running weird Powershell scripts that launch the Wizard via the commandline. None of them worked for me. What worked was the advice on this page:
Although useful, note that the thread contains some unkind words to one of the thread participants. It also spreads out the advice I ended up using across the discussion.
The thread is full of good insight, and well worth reading. However, I thought it would be helpful to consolidate what I ended up using to solve our issue.
First, the problem: WSUS uses an internal database to store its updates. The Server Cleanup wizard calls a stored procedure which tries to expire all old updates at once, and this times out and makes the Cleanup Wizard hang. Usually, we want atomic all-or-nothing operations in databases, so when the expiry times out the database helpfully rolls back expired updates, leaving us in the position we started.
In addition to having too many updates it appears that certain obsolete updates have a large number of revisions, which also slows the clearout process down. Once these troublesome updates are expired, the rest of the cleanup can be handled relatively efficiently.
To solve the problem, you log into the database directly and run a script that expires updates one by one. Troublesome updates take a long time to expire, but they don't hit the timeout value, so eventually all updates get cleared.
Second: the steps. This boils down to running the following script,
provided by user __Russ__ on the forums, based on code by
vexation. (Sorry about the long line. I do not know how to safely
insert linebreaks into the script and am not going to bother finding
out.)
--************** 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
Download SQL Management Studio Express . We are running WSUS on Server 2008 R2, so I got version 2005, available here: http://www.microsoft.com/en-us/download/details.aspx?id=8961
Install SQL Management Studio Express and then start it as administrator. Set the server name to be
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query. Apparently for Server 2012 this should be\\.\pipe\MICROSOFT##WID\tsql\query.Navigate to
Databases -> SUSDB.Make a "New Query".
Paste the script above into a query window. Then click "Execute".
Wait a long time. The script will tell you what it is doing in the "Messages" tab, which is helpful.
When the script has finished executing your WSUS installation should be clean.
Run the Cleanup Wizard regularly (I have decided to run it every two months) in a vain attempt to keep the database clean from now on.
Later in the thread there is some Powershell script that is supposed to do the same thing, but it did not work for me.
There a few takehome lessons for me from this:
Opaque software is terrible. The only reason people were able to solve his problem was because WSUS (which I treat as a black box, since we auto-approve updates) actually uses SQL server secretly, and it is possible to connect to the SQL server directly and fix things. If that SQL interface was not accessible we would all be stuck. I run into this issue again and again and again. Software that you cannot troubleshoot and poke at is software you can't fix.
Similarly, uninformative progress bars that hide system problems are terrible. There should always be an option to see what the program is doing under the hood, if only so we can see whether the program is actually making progress. Progress bars lie.
Cooperative threads where people contribute and improve collective knowledge about problems are super-valuable, even when valuable information is smeared across the thread. I am so grateful that these sysadmins investigated the problem thoroughly and solved it, and I think Microsoft is as well: recently they published a long article http://blogs.technet.com/b/configurationmgr/archive/2016/01/26/the-complete-guide-to-microsoft-wsus-and-configuration-manager-sup-maintenance.aspx which FINALLY addressed this problem. I doubt they would have done so if the above thread had not existed.
This kind of thread is probably not going to be successful on Stack Overflow, because Stack Overflow expects fully-formed solutions, not partial answers that work towards a solution. Overall I vastly prefer Stack Overflow results to the awful Microsoft forums, but in this case the Microsoft forum worked better.
WSUS is kind of terrible. I appreciate the declining individual updates is a useful service, but I mostly want a stable, on-site cache of Windows updates that our computers can use without taxing our external internet connection. But as updates get larger and more numerous, WSUS is showing its age more and more. It is also not fire-and-forget software, which is really irritating. On the other hand, WSUS is gratis with Windows Server. Having to purchase System Center Configuration Manager to get a Windows Updates cache would be worse.