In the past we have produced several popular articles explaining how to cleanup and fix Windows Server Update Services (WSUS):
How to Change WSUS To Download Patches Directly from MS and Not Store Updates Locally
How To Install & Configure WSUS on Windows Server 2012R2 or Server 2016 in 20 Minutes
The Ultimate Guide To Fixing 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:
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’.
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
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:
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:
Rerun that SQL query over and over until it returns 0 ROWS AFFECTED.
At this point you are mostly done but there are some small things left to wax the job.
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
Just what it says. Run it again; it will be fast.
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:
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.
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!
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:
Once that is done I run the WSUS cleanup wizard again to unload that junk before it clogs up my system again.
This website uses cookies.
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 :)
what is '3+M' ?
Suggestion for section 4 - if you are confident you wont crash your server, update "IF @curitem < 250" to "IF @curitem < @totaltodelete +1"
Hi Ryan;
Thanks, I added that line right into section 4.3 :)
Better yet - "IF @curitem <= @totaltodelete"