SQL Commands To Clean Up WSUS Database 

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

There are three things (as far as we have figured out!) you need to know about this script before running it:

  1. Make sure you have created and updated the WSUS indexes as noted in steps 1 and 2 above
    • Those indexes will allow this script to delete the junked updates at a rate of about 1 update for every 2 seconds but if you haven’t got those indexes setup it will take about 1 minute per update… not good.
  2. The script will almost certainly need to be run multiple times until all the old patches are deleted
    • In this case we have it set to 250 which means it takes about 5 minutes per execution
    • If we had 10,000 patches to delete it that means we would need to run the query 40 times
  3. Change the number at the end of the line “IF @curitem < 250” to change the number of updates this script will delete each time you run it
    • ONE OF OUR READERS SUGGESTED THIS LINE:
      if you are confident you wont crash your server, update “IF @curitem < 250″ to “IF @curitem < @totaltodelete +1”
    • We suggest you start by setting that number to just 10 for your first round, because if your indexes are not setup (see steps 1 and 2 above) then it will take about 10 minutes to complete and you will be sad
    • After you run it at 10, and find that it takes about 15 seconds to run, you may want to change the number to 250 for your next run, and then to 2000 after you are comfortable you are not pinning out your server

Leave a Comment