WSUS Delete Obsolete Updates

NOTE: Usual warnings apply. Do a backup before making any changes. If you are unsure about anything in the post then ask or look for more information or help before attempting it.

Over time WSUS will accumulate update metadata that can create performance issues for clients. In large environments this can be quite an issue.

There is a script Microsoft often provides during Premier Support calls to cleanup this update metadata, however there are a few issues:

• The query can take a *really* long time to run if there are a lot of updates to cleanup. In some cases it can take *days*
• You need to stop all the WSUS services while it runs
• If it fails for whatever reason, it will have to start all over because it doesn’t commit the changes until it completes successfully
• While it runs, the TEMPDB and Transaction logs will grow quite significantly until the data is committed
• It gives no useful information on progress

There is a TechNet article (This is essential reading and has LOTS of important stuff) and a Forum Post where an improved version was written that gave progress of the cleanup, however it didn’t address the temp/transaction growth issues or the time issues. To this end I have applied my very rudimentary SQL scripting skills.

To find out just how many updates are waiting to be cleaned up, run this stored procedure:

EXEC spGetObsoleteUpdatesToCleanup

Firstly, when the script runs on a default WSUS install it can take over a minute to process *each* record. If there are thousands or tens of thousands or updates to remove this is going to take a while. There is an index you can add to the WSUS table that dramatically improves this so it happens at about 1 second per record. Microsoft confirmed this index is OK, however it is not officially supported (at time of writing)

USE [SUSDB]
GO
CREATE NONCLUSTERED INDEX [IX_tbRevisionSupersedesUpdate] ON [dbo].[tbRevisionSupersedesUpdate]([SupersededUpdateID])
GO
CREATE NONCLUSTERED INDEX [IX_tbLocalizedPropertyForRevision] ON [dbo].[tbLocalizedPropertyForRevision]([LocalizedPropertyID])
GO

Now to the cleanup script. Simply this script will cleanup obsolete records, provide progress feedback and also allow you to run it in small blocks. This allows you to run in short blocks without needing to stop the WSUS server and avoids generating huge transaction loads on the SQL server.

To “tweak” the script, modify this line with the number of updates you want to do in each block. Start with 50, see how it runs in your environment and increase as needed. Ideally don’t run batches that take more than 5-10 minutes to prevent those SQL transaction logs growing.

IF @curitem < 101

If you do want to run a larger batch that may take hours, you should of course stop the WSUS services to do so. Also, don’t run this script if a WSUS Sync is in progress or scheduled to start.

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
IF @curitem < 101
 FETCH NEXT FROM WC INTO @var1
END
CLOSE WC
DEALLOCATE WC
DROP TABLE #results

If for any reason the script is interrupted, you will find SQL still has the transaction table open and won’t let you run again (There is already an object named ‘#results’ in the table). To resolve this highlight and execute the last line to drop the table.

If this still doesn’t help, close the SQL Studio Manager session and you should be prompted with a warning about uncommitted transactions. Select Yes to commit then reopen and start the query again.

If for any reason the query is not properly closed there may be locks held on the SQL database that will prevent the normal WSUS service functioning resulting in failure of service.