We use WSUS in our own datacenter as well as customer sites, for many client based scenarios this is done using WSUS and ConfigMgr, but in the fabric it is either WSUS or WSUS and SCVMM. When WSUS is used for content and distribution (Nativly or with SCVMM) it needs a helping hand…
- Someone need to deny all patches that are superseeded, this does not happen automatically.
- Someone needs to cleanup old content, computers, patches and such, this does not happen automatically.
- Someone needs to care for the database, this does not happen automatically.
So, over the years poeople around the globe has been providing scripts for this, and here is what we currently are using.
https://github.com/DeploymentBunny/Files/tree/master/Tools/Invoke-WSUSMaint
The script will do the following
Connect to a database
you might need to change this in the script.
#For Windows Internal Database, use $WSUSDB = ‘\\.\pipe\MICROSOFT##WID\tsql\query’
#For SQL Express, use $WSUSDB = ‘\\.\pipe\MSSQL$SQLEXPRESS\sql\query’
Get the Superseeded Updates
Here is the Posh that fixes that:
$SuperSeededUpdates = Get-WsusUpdate -Approval AnyExceptDeclined -Classification All -Status Any | Where-Object -Property UpdatesSupersedingThisUpdate -NE -Value ‘None’ -Verbose
$SuperSeededUpdates | Deny-WsusUpdate –Verbose
Cleanup WSUS
We run each step sepratly, however, you can change that and run everything in one line…
Cleanup the DB
Last part runs sqlcmd using a .SQL file from MSFT Gallery, and yes, you can download and install the PowerShell tools for SQL and use that instead. Most of your customers dont have thoose tools installed, so sqlcmd.exe it is.
Share this:
Categories: Datacenter, Patching, PowerShell, WSUS
The Deployment Bunny 
Get-WsusUpdate is only valid on Server 2012, right?
We’re still (and probably many with us) on 2008 R2.
Correct, upgrade is an option, takes less then an hour to build a new server, resync. We use PowerShell for that, in Deployment Fundamentals vol. 5 All scripts needed to build a complete infrastructure is included, so if you want it, just do it…
Thanks, but need time to upgrade to 2012. But at least it’ll happen on this side of summer :)
A M A Z I N G ! I have spend the last month googling what caused the problem of my clients needing 25+ cycles of [wuauclt /detectnow] (getting 0x80244010 Exceeded max server round trips) to get in sync with my freshly build 2012 R2 WSUS server.
I had 1100+ Win 7 systems, all moaning after a reset of their client. WSUS server only had 5000 updates for Win7 and office. Before I ran this script I had 4822 of 5005 in the All Updates section. After running the script 1807 of 5005. Clients now only need one cycle to get their updates.
Please let me know where I can send a big thank you cake, as this really has caused me a lot of headache and time!
I used this script but it declined all Updates and not only supersedded.
Any idea ?
The powershell script looks for anything that has been superseded and declines them. But it also looks for unused, not needed and a bunch of other things as well. So, the only reason it would remove updates is that if they are not needed.
It seems that your condition is always true with French Windows Server 2012 R2 because it isn’t ‘None’ but ‘Aucun’.
That could be true, I have never seen a French Windows Server, I always run them in English, sorry about that.
Hi Mikael,
This was an interesting script.
I have 2 questions on this because I cannot get this script to work for me:
1. Line 28 gives me this error?
$RunningFromFolder = $MyInvocation.MyCommand.Path | Split-Path -Parent
Split-Path : Cannot bind argument to parameter ‘Path’ because it is null.
At line:2 char:53
+ $RunningFromFolder = $MyInvocation.MyCommand.Path | Split-Path -Parent
+ ~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [Split-Path], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.SplitPathCommand
2. Iam on an 2012R2 server with the native WSUS database engine, where can I find the correct sqlcmd.exe to use for this, I have not this Exe located on this server?
Ideas?
Yes, so you need to run this from the PowerShell prompt, not in ISE, since MyInvocation does not work there by default. As far as I remember SQLKCMD is installed when you add the database if not, just install any SQL basic management tool, like Microsoft® SQL Server® 2012 SP1 Feature Pack
Hi Mikael,
Thanks for your fast reply.
You were spot-on about me using ISE to parse errors.
I did not know that PowerShell and PowerShell ISE had different ways of working, thought it was the same…
After the first run of the script the script cleaned about 60GB of data, from 80GB to 20GB.
Now I run the same scripts again and again, it takes about 40 minutes every run to complete, is this normal that it takes this long for every run?
I can see that “SQL Server Windows NT – 64 Bit” and “Windows Powershell” works at under 10% CPU during this time.
I know this is relevant to the hardware and load of cource, but i am using a HP G7 Server and not much load at all so this feels pretty strange or?
Also sqlcmd.exe version 2005.90.4035.0 gives me an error (APPCRASH) at the end (after 40 minutes) saying “T-SQL execution command line utility has stopped working”
Any ideas forward?
Here are som extra info:
Also tried with sqlcmd.exe version 2009.100.4042.0 but still received the same APPCRASH.
The same happens also with version 2009.100.2500.0.
Any ideas further from anyone on this APPCRASH?
Any comment on the time 40 minutes to run this script before it comes to the APPCRACH part in the end (seems very slwo to me)?
I see a lot of processes for WSUS that either ignore superseded or, such as in this case, get rid of superseded updates. However, this can be extremely dangerous, especially in scenarios where updates have been superseded by service packs that cannot be deployed into production right away…or really in any case where the replacement isn’t production ready.
Assume a security update is superseded on patch Tuesday, you run this process that Wednesday, but you take two weeks to deploy patches to your production systems. What happens when you build a system in that window?
In that case nothing, the new patch will be installed since I run Windows update as a part of OSD, so it will have the new patch instead of the old one. But it does depends, in this case we automatically approve the new patch at the same time as we remove the superseded, you could also extend the script to check each superseded patch and before decline it, you check if the new patch has been approved or not.
Anybody else get a timeout on the “Cleanup Unneeded Content Files” step?
****************************************
Cleanup Obsolete Computers
Obsolete Computers Deleted:92
Cleanup Obsolete Updates
Obsolete Updates Deleted:557
Cleanup Unneeded Content Files
Invoke-WsusServerCleanup : The operation has timed out
At E:\Maintenance\Invoke-WSUSMaint.ps1:59 char:32
+ $CleanupUnneededContentFiles = Invoke-WsusServerCleanup -UpdateServer $WSUSSrv – …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (Microsoft.Updat…rCleanupCommand:InvokeWsusServerCleanupCommand) [Invoke-
WsusServerCleanup], WebException
+ FullyQualifiedErrorId : ServerIsInvalid,Microsoft.UpdateServices.Commands.InvokeWsusServerCleanupCommand
Compress Updates
Updates Compressed:14620
Decline Expired Updates
Expired Updates Declined: 0
Decline Superseded Updates
Obsolete Updates Deleted:0
Defrag and Cleanup DB
Changed database context to ‘SUSDB’.
Estimating fragmentation: Begin. 2017-04-24 16:55:41.240
Number of indexes to rebuild: 75
Estimating fragmentation: End. 2017-04-24 16:55:43.373
2017-04-24 16:55:43.380 Executing: ALTER INDEX [PK__tbPrereq__25A953F9C1386C5F] ON [dbo].[tbPrerequisite] REORGANIZE
****************************************
Yes, that happens when there are to many updates to deal with, just run it once more. This should be something to run every month or so…
Thanks!
That explains why it recently started happening after running smoothly for quite a while. My current work around has been to run in it twice but I thought I should make sure I wasn’t missing anything.
I actually do run this once a month and have since I stood the server up well over a year ago; thanks for the scripts! :-)