Whenever we do a cleanup on WSUS
we always get the below error.
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. we had been running it as a
"fire and forget" server: allow it to auto approve 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. We 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
command line. Most of the time noting will work
Finally we found the below script
which will help to do the cleanup through SQL
- ·
We can’t
access WSUS database through remote SQL Management Studio, so we have to
install SQL Management Studio locally.
- ·
To connect
SUSDB - \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query For Server 2012 \\.\pipe\MICROSOFT##WID\tsql\query.
- ·
Make a New
Query
Run the
below query to check the status
exec spGetObsoleteUpdatesToCleanup
Again execute
the below query to clean up the unapproved, superseded and obsolete updates.
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
Now check if
you see any update using this query - exec
spGetObsoleteUpdatesToCleanup
Finally, run
the WSUS cleanup wizard now, you will not get any errors now.
