Categories: Windows Server

SOLVED: Easy SQL Commands To Clean Up WSUS Database Quickly

In the past we have produced several popular articles explaining how to cleanup and fix Windows Server Update Services (WSUS):

However, last week ran into a problem with one client whose WSUS was so burdened by unnecessary patches the WSUS native tools (including the command line tools) had taken over a week to run and we still had no idea how long it was going to take to complete.

By “unnecessary patches” we mean that whoever setup the WSUS selected all PRODUCTS and all CLASSIFICATIONS.  We made the appropriate corrections and got it down to the dozen or so PRODUCTS we cared about, but now we had to delete the garbage to get WSUS fully functional.

In our case the database was running on a full SQL server which was different from where WSUS had been installed.  We RDP’d to the SQL server and ran the following cleanup commands (all of which we took verbatim from various Microsoft articles) right in SQL MANAGEMENT STUDIO:

1 – Create Special WSUS Indexes So the Next Queries Run Fast

Download this script to create special indexes for WSUS Database HERE

If the indexes were already created, then you will get an error (which you can safely ignore) like :

Msg 1913, Level 16, State 1, Line 4
The operation failed because an index or statistics with name ‘nclLocalizedPropertyID’ already exists on table ‘dbo.tbLocalizedPropertyForRevision’.

2 – Force a Reindexing of the Entire WSUS Database

Download this script to reindex WSUS database HERE.

On my clients server this took more than an hour to complete.  I got very worried when the MESSAGES tab (SQL Management Studio) did not add anything for about 30 minutes, but I could see SQL was still grinding my CPU in Task Manager

For more details on this script see THIS Microsoft article

3 – Find Out How Many Patches Are Superseded:

This query just takes a few seconds because it is just a count:

Select COUNT(UpdateID) from vwMinimalUpdate where IsSuperseded=1 and Declined=0

You might also find it interesting to check the FREE SPACE on the database by:

  1. Launch SQL SERVER MANAGEMENT STUDIO on the SQL SERVER
  2. Expand DATABASES
  3. Right click on the SUSDB and see what FREE SPACE is

4 – SQL Command To Delete Updates From WSUS SUSDB Database

Download this SQL Command To Delete Updates From WSUS SUSDB Database HERE but read the notes under this screenshot BEFORE you run it.

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

Rerun that SQL query over and over until it returns 0 ROWS AFFECTED.

5 – Wrap up!

At this point you are mostly done but there are some small things left to wax the job.

5A – Script to Clean Up WSUS

Now that you have SQL cleaned up you can download the Decline-SupersededUpdatesWithExclusionPeriod.ps1 powershell WSUS cleanup command right HERE. 

NOTE – Rename the file to end with .PS1 extension and then run it in a PowerShell command line as an Administrator

This command took about 10 minutes to run on my servers and it got slower as the it got closer to finishing.

Decline-SupersededUpdatesWithExclusionPeriod.ps1 -UpdateServer SERVERNAME -Port 8530

In my case I reduced the number of updates to just 8400 from 40000.

5B – Rerun Step 2 Again To Get That Index Current

Just what it says.  Run it again; it will be fast.

5C – Check to See How Many Old Updates Still Remain

At this point you can go back to step 3 and see how many old updates still exist in the database.  It should be zero.

You might also find it interesting to check the FREE SPACE on the database by:

  1. Launch SQL SERVER MANAGEMENT STUDIO on the SQL SERVER
  2. Expand DATABASES
  3. Right click on the SUSDB and see what FREE SPACE is

In my case after nearly a week of trying to clean the database using the normal scripts and the SERVER CLEAN UP WIZARD, the SUSDB changed from 200MB free to 425MB free.  After running the above scripts (just two hours of work) the SUSDB changed from having 425MB free to 4500MB free.

After I ran through the next two items, I checked the SPACE AVAILABLE again, and found it was now at 6600MB.  That is an impressive amount of space to get back on a SQL database.

5D – Run the WSUS SERVER CLEAN UP WIZARD

Microsoft recommends you finish off all these scripts with a final coat of wax by running the  WSUS SERVER CLEAN UP WIZARD (UNDER OPTIONS at the bottom of the left side menu, in WSUS).  It won’t be instant but it will move and should finish within 10 minutes.  The one in this screenshot took 4 minutes to complete and you can see the massive 92GB of disk space I got back!

5E – Niceties

Now that your WSUS is functional again, it is a good time to manually DECLINE junk that you office is never going to use.  For instance, my offices have no ARM64 devices, so I search in the WSUS interface, I do a search (to right) for text that contains ARM64, then I select all of them and decline them.  I do the same thing for:

  • OFFICE XP
  • Windows XP
  • Windows Vista
  • Windows Embedded Standard 7
  • Media Center
  • Exchange Server 2003
  • Exchange Server 2010
  • Itanium (in case you are wondering Itanium is an old CPU technology)
  • and any other ancient or junk apps you notice

Once that is done I run the WSUS cleanup wizard again to unload that junk before it clogs up my system again.

View Comments

  • Hi

    thanks for your script
    maybe this can be useful for someone who want use sync drivers in his WSUS
    mine was constantly crashing during the spCompressUpdate phase due to the insane number of revision for each driver.
    after looking the database, I've found around 200k updates for 3+M revisions
    I have altered your script from section 4 to do it with the revisions. it took me around 8 hours to complete but the console is working fine now

    here the code

    USE SUSDB
    DECLARE @var1 INT, @curitem INT, @totaltodelete INT
    DECLARE @msg nvarchar(200)
    CREATE TABLE #results (Col1 INT) INSERT INTO #results(Col1)
    EXEC spGetUpdatesToCompress
    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 spCompressUpdate @localUpdateID=@var1
    SET @curitem = @curitem +1
    IF @curitem < 250
    FETCH NEXT FROM WC INTO @var1
    END
    CLOSE WC
    DEALLOCATE WC
    DROP TABLE #results

    hoping it can help some people :)

  • Suggestion for section 4 - if you are confident you wont crash your server, update "IF @curitem < 250" to "IF @curitem < @totaltodelete +1"

Leave a Comment
Published by
Ian Matthews
2 years ago

This website uses cookies.