WSUS Delete Obsolete Updates

Posted: August 15, 2017 in Configuration Manager, Information, SQL, Windows Update
Tags: , , ,

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)

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

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.

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
BEGIN SET @msg = cast(@curitem as varchar(5)) + '/' + cast(@totaltodelete as varchar(5)) + ': Deleting ' + CONVERT(varchar(10), @var1) + ' ' + cast(getdate() as varchar(30))
EXEC spDeleteUpdate @localUpdateID=@var1
SET @curitem = @curitem +1
IF @curitem < 101
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.


  1. Tom Dub says:

    You can alos run this (as suggested by MS) on the WSUS server

    [reflection.assembly]::LoadWithPartialName(“Microsoft.UpdateServices.Administration”) | out-null
    $wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer();
    $cleanupScope = new-object Microsoft.UpdateServices.Administration.CleanupScope;
    $cleanupScope.DeclineSupersededUpdates = $true
    $cleanupScope.DeclineExpiredUpdates = $true
    $cleanupScope.CleanupObsoleteUpdates = $true
    $cleanupScope.CompressUpdates = $true
    #$cleanupScope.CleanupObsoleteComputers = $true
    $cleanupScope.CleanupUnneededContentFiles = $true
    $cleanupManager = $wsus.GetCleanupManager();

    • Scott says:

      Yes, that will trigger the normal WSUS clean up actions, but it will timeout/crash if there are a large number of obsolete updates to be cleaned. As I also mentioned the SQL server transaction and tempdb files will keep growing while that clean up runs and can cause significant resource issues.
      The reason I gave this example is to allow you to do the clean up in smaller batches that doesn’t overwhelm the SQL server itself.

  2. Jeff says:

    i cannot get it to run any faster than 2 minutes a deletion. i have ran the;

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

    and it completes sucesfully, but still only deleting 1 every 2 minutes.

    1/49101: Deleting 1645689 Apr 24 2018 3:37PM
    2/49101: Deleting 1645650 Apr 24 2018 3:38PM
    3/49101: Deleting 1645615 Apr 24 2018 3:40PM
    4/49101: Deleting 1645580 Apr 24 2018 3:42PM
    5/49101: Deleting 1645545 Apr 24 2018 3:44PM

    • Scott says:

      You have a *lot* of updates for it to work through. Unless you can add more memory or CPU to that SQL server it will probably take a while to work through that many deletes. I’d suggest doing batches of 1000 at a time outside business hours until you get them all clear. As the number to delete gets smaller you should see the speed improve.
      Remember, don’t run it while a DB sync is going to run, and watch the memory/CPU utilisation on the SQL server to make sure it isn’t impacting your normal client activity.

      • Jeff says:

        Will do. Thank you very much.

        I have been running small batches and the last couple have had a few 1 minute deletions.

        • Scott says:

          Excellent. Keep it up, take it easy and don’t rush it. Once you have it under control it will be much easier to keep it sorted out.

  3. […] Wsus Delete Obsolete Updates – where the key is the addition of the added indexes to SUSDB. […]

  4. Rienus says:

    Perfect, works fine for me! Thanks 😉

  5. Chris says:

    This page saved me so much pain.
    Everything worked a treat, I now have a working WSUS DB again.

    What I would add is, If like me you hit this issue because the Database exceeded the 10GB license limit, you would need to Shrink the database after.

    maybe add pdateing statistics via
    EXEC sp_updatestats

    Thanks again,

    • Scott says:

      What is the 10GB license limit? Is this from the Windows Internal Database? I’ve never run a WSUS server using it so I haven’t encountered that before.

  6. boss01 says:

    Nice explanation!,

    The SQL Query provided in the below link is taking a lot of time to execute, moreover, there are no instructions to stop any services before running the query!!

    did anyone ran this query for WID (susdb)?
    my db is 40 gb
    is it ok to stop the query once its fired?

    • Scott says:

      It is OK to stop the query, however the transaction will all be rolled back (which itself can take a long time) and you’ll have to start over. That’s why I use the script that lets you do it in small blocks. Start with 10 and see how long it takes, then do 100. From that you will be able to work out what times work best.
      When I started a big cleanup I wouldn’t do any more than 30 minutes worth at a time. The SQL server has to do a lot of work and cleanup to process this and it could take weeks to clean it all up if it hasn’t been done before. Once sorted out things will go *much* faster for future cleanups.

  7. Ted Statham says:

    Thank you for putting this together. The script was invaluable to me in my recent efforts to clean up an older WSUS 3 installation running on Windows Server 2008 R2. It took a week or so of evenings to clear out 28,000+ obsolete updates. During that time, the WSUS database freed up about 15GB of space, ultimately letting me shrink it from 30GB to about 15GB. As well, the time to delete an individual update dropped from about 65 seconds to about 8 seconds.

    I also figured out how to identify and finally delete the files related to those obsolete updates, clearing 440,000 files and 1TB of disk space from a nearly full 1.6TB partition. For those interested, you can read the details over at my blog:

    • Scott says:

      Good effort there. WSUS really does need some product improvements to avoid this ever becoming an issue, hopefully something is coming along sometime soon.

  8. Dustin says:

    This runs for me but I have a problem with 317 remaining rows not being deleted. The errors I get (for each row in the 317 rows) is:

    (317 rows affected)
    1/317: Deleting 496148 Dec 10 2018 3:52PM
    Msg 50000, Level 16, State 1, Procedure spDeleteRevision, Line 33 [Batch Start Line 0]
    Msg 50000, Level 16, State 1, Procedure spDeleteUpdate, Line 72 [Batch Start Line 0]
    spDeleteUpdate got error from spDeleteRevision
    2/317: Deleting 486347 Dec 10 2018 3:52PM
    Msg 50000, Level 16, State 1, Procedure spDeleteRevision, Line 33 [Batch Start Line 0]
    Msg 50000, Level 16, State 1, Procedure spDeleteUpdate, Line 72 [Batch Start Line 0]
    spDeleteUpdate got error from spDeleteRevision
    3/317: Deleting 483071 Dec 10 2018 3:52PM

    This goes on and on until the end. Does anyone have a ‘delete row’ script where I can take the output from exec spGetObsoleteUpdatesToCleanup and just destroy these 317 records?


    • Scott says:

      You can run getobsolete by itself, it just returns a list of update numbers. You can then run spdeleteupdate by itself with the update number to delete just that update. (check script for syntax)
      It might be worth stopping all WSUS services to make sure there are no locks or other processes trying to access the DB.
      If it persists, you may have a corruption or some other issue.

  9. Scott says:

    Thank You! This is excellent.

    I wanted to give feedback that I found the deletes were much faster on my system when deleted oldest first. So changing

    DECLARE WC Cursor FOR SELECT Col1 FROM #results


    DECLARE WC Cursor FOR SELECT Col1 FROM #results ORDER BY Col1

    gave me much faster batches.

  10. Georges says:

    First off, this page is amazing – THANK YOU. I ran the script little by little ( < 25, then < 35, then < 50) and it cleaned out hundreds of expired/declined "Microsoft Endpoint Protection" updates. I was wondering if there is a change in the script to clean out expired/declined "Windows Defender Antivirus" updates as well? Microsoft decided to rename the antivirus for W2016 😦

  11. Nicke Källén says:

    I ran this as SQL Agent Job, and for some reason the 2 minute delay I noticed during a simple New Query wasn’t noticed.
    I Think I timed each delete to 160 seconds, whereas the SQL Agent Job blasted through 100/6minutes

  12. […] the spGetObsoleteUpdatesToCleanup stored procedure to count obsolete update. I have over 27,000. This article is a gem for fixing that. I’m copying scripts verbatim to make sure I can hang on to […]

  13. Fred says:

    I recently had this exact issue with an older SCCM installation. I was able to set the count up to 1001, getting about 28 Updates per Minute removed. SUSDB had corrupted indexes and around 7500 obsolete updates. Was time for some action 🙂

  14. Fred says:

    I can confirm Scott’s acceleration solution btw. Changing the line to “DECLARE WC Cursor FOR SELECT Col1 FROM #results ORDER BY Col1” gets me to 142 Updates per minute with IF @curitem < 1001 😀

  15. Ian North says:

    I’m running WSUS on Windows Server 2016 and SQL 2016 with the latest SP and CU15. The server has around 30000 obsolete updates to clear. I’ve added the extra indexing tables and have tried the modification that Scott suggested above. I’ve reindexed the databaseand rebuilt its indexes. However, the server is still taking 1-2 minutes per update to clear. Does anyone have any suggestions on what to try next?

    I’m half tempted just to give up with the damned thing and start afresh, to be honest. At least then I can schedule these cleanup scripts on a fresh database!

  16. […] That’s going to take way too long… researching the stored proecdure in question I found this Blog post. […]

  17. Agatha Burg says:

    Hmm… it was working perfect until I decided to shrink the database.
    Now when I run the query, even for 10 records it doesn’t show any results just spinning in execution cycle.
    Do you have any ideas how to troubleshoot it?
    Thank you.

    • Scott says:

      Check your SQL server has enough disk space for the Temp DB’s. Also try deleting just one update.
      It may be that if there are still a lot of obsolete updates it is going to take a long time again to rebuild indexes etc.

      • Agatha Burg says:

        Thanks for your suggestion. I actually had to rebuild the indexes and now I can delete 1000 updates for minutes.
        No I have to figure out how to delete unneeded files and free space. I hope @Ted Statham suggestion will work.
        Thanks again for writing and sharing the script.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s