WSUS Cleanup Using SQL Script

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

1. ·         We can’t access WSUS database through remote SQL Management Studio, so we have to install SQL Management Studio locally.
2. ·         To connect SUSDB - \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query For Server 2012 \\.\pipe\MICROSOFT##WID\tsql\query.
3. ·         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.