Metanis
Well-Known Member
- Reaction score
- 941
- Location
- Medford, WI, USA
At the beginning of this year (2013) I installed a Windows Server 2012 box and configured it to maintain my home and small shop domain. I installed WSUS and enabled many of the products and options so that I could use it to quickly install updates on bench machines that required rollbacks or fresh installs. (I use D7 to do the registry changes quickly and easily on a customer's PC).
This setup worked pretty sweet until I recently tried to run the WSUS Server Cleanup script and it would fail with server timeouts. It turns out I had something like 30,000 updates approved and downloaded. The SUSDB database had grown to unwieldy proportions for the old P4 server and queries were timing out due to the number of records which the Windows Internal Database needed to manage.
In solving this problem I found some excellent resources on the web and I want to share these in case you too are responsible for maintaining a WSUS server. The first and probably most important was the blog site:
http://thwack.solarwinds.com/commun...errors--when-and-why-eliminating-and-avoiding
by a person with the handle LGarvin. That blog thread has 5 separate posts on managing the Approval process to minimize the number of records your SUSDB needs to maintain. Probably the most important take-away is the necessity to Decline updates which have been superseded! I never knew there was a column which could be enabled called "Supersedence". With that column enabled it's easy to sort and find all the updates which had previously been enabled and installed but were now superceded by a newer update. By Declining those updates it begins the process where they can be removed from the server and both the database and hard drive space recovered.
From reading LGarvin's articles I realized my biggest mistake was enabling WSUS to download and distribute the Drivers classification. This resulted in 22,000 drivers being enabled and downloaded. That made up the largest single source of database records and it was of almost zero utility on a regular basis. During this phase of my research I came across another blog entry at:
http://runesk.blogspot.com/2012/09/delete-oldunwanted-updates-from-wsus.html
by Rune Nordbøe Skillingstad which documented a method to remove the unwanted Product category updates from the SUSDB database. The Powershell script provided by a commenter named "krumpaul" worked perfectly to remove those 22,000 unnecessary records.
Further research on using PowerShell to clean-up unnecessary records resulted in another blog posting which completed my quest to minimize the footprint required by WSUS. This blog post at
http://www.flexecom.com/how-to-delete-driver-updates-from-wsus-3-0/
by Dennis Suhanovs provided the PowerShell script to delete ALL Declined updates from the WSUS SUSDB database in the section of the post called "A PowerShell Afterthought". He has examples of the scripts at his Download link http://www.flexecom.com/downloads/
I also implemented the WSUSDBMaintenance.sql script which the Microsoft Technet Script guys came up with a few years ago. After taking all these steps I'm down to 4,000 update approvals and I reclaimed about 200GB of disk space but more importantly the WSUS Admin console is usable (almost perky!) again with no timeout errors.
Here's a summary of all the steps I took to clean my mess up.
1) Turned on the Supersedence column and used it to find and Decline all superceded updates of every kind.
2) Tightened down my Microsoft Product selections to more closely match my "routine" customer machine. This meant de-selecting whole categories like "Drivers".
3) Changed my Auto-Approval rule to Definitions only. (Yes, I'll need to manually check the server much more often to approve real updates.)
4) Sorted updates by Classification - Drivers and declined them all.
5) Ran the following PowerShell script as Administrator to remove the Driver records (it took all night!):
[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | Out-Null
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer()
$wsus.getupdates() | Where {$_.UpdateClassificationTitle -eq 'Drivers'} | ForEach-Object { $wsus.DeleteUpdate($_.Id.UpdateID); Write-Host $_.Title removed }
6) Ran the Server cleanup wizard and it was finally able to run successfully.
7) Ran the following PowerShell script as Adminstrator to remove ALL Declined Update records as I found this after further research:
[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer();
$wsus.GetUpdates() | Where {$_.IsDeclined -eq $true} | ForEach-Object {$wsus.DeleteUpdate($_.Id.UpdateId.ToString()); Write-Host $_.Title removed }
At this point WSUS is much more responsive and still meets my need to deliver probably 95% of all updates to any Windows XP, Vista, 7, or 8 machine I put on my bench along with most common Microsoft add-ins like Office, Silverlight, Bing, Live, etc. (This WSUS box also serves my home personal machines on an "everyday" basis.)
-Mike Tanis
Medford, WI
This setup worked pretty sweet until I recently tried to run the WSUS Server Cleanup script and it would fail with server timeouts. It turns out I had something like 30,000 updates approved and downloaded. The SUSDB database had grown to unwieldy proportions for the old P4 server and queries were timing out due to the number of records which the Windows Internal Database needed to manage.
In solving this problem I found some excellent resources on the web and I want to share these in case you too are responsible for maintaining a WSUS server. The first and probably most important was the blog site:
http://thwack.solarwinds.com/commun...errors--when-and-why-eliminating-and-avoiding
by a person with the handle LGarvin. That blog thread has 5 separate posts on managing the Approval process to minimize the number of records your SUSDB needs to maintain. Probably the most important take-away is the necessity to Decline updates which have been superseded! I never knew there was a column which could be enabled called "Supersedence". With that column enabled it's easy to sort and find all the updates which had previously been enabled and installed but were now superceded by a newer update. By Declining those updates it begins the process where they can be removed from the server and both the database and hard drive space recovered.
From reading LGarvin's articles I realized my biggest mistake was enabling WSUS to download and distribute the Drivers classification. This resulted in 22,000 drivers being enabled and downloaded. That made up the largest single source of database records and it was of almost zero utility on a regular basis. During this phase of my research I came across another blog entry at:
http://runesk.blogspot.com/2012/09/delete-oldunwanted-updates-from-wsus.html
by Rune Nordbøe Skillingstad which documented a method to remove the unwanted Product category updates from the SUSDB database. The Powershell script provided by a commenter named "krumpaul" worked perfectly to remove those 22,000 unnecessary records.
Further research on using PowerShell to clean-up unnecessary records resulted in another blog posting which completed my quest to minimize the footprint required by WSUS. This blog post at
http://www.flexecom.com/how-to-delete-driver-updates-from-wsus-3-0/
by Dennis Suhanovs provided the PowerShell script to delete ALL Declined updates from the WSUS SUSDB database in the section of the post called "A PowerShell Afterthought". He has examples of the scripts at his Download link http://www.flexecom.com/downloads/
I also implemented the WSUSDBMaintenance.sql script which the Microsoft Technet Script guys came up with a few years ago. After taking all these steps I'm down to 4,000 update approvals and I reclaimed about 200GB of disk space but more importantly the WSUS Admin console is usable (almost perky!) again with no timeout errors.
Here's a summary of all the steps I took to clean my mess up.
1) Turned on the Supersedence column and used it to find and Decline all superceded updates of every kind.
2) Tightened down my Microsoft Product selections to more closely match my "routine" customer machine. This meant de-selecting whole categories like "Drivers".
3) Changed my Auto-Approval rule to Definitions only. (Yes, I'll need to manually check the server much more often to approve real updates.)
4) Sorted updates by Classification - Drivers and declined them all.
5) Ran the following PowerShell script as Administrator to remove the Driver records (it took all night!):
[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | Out-Null
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer()
$wsus.getupdates() | Where {$_.UpdateClassificationTitle -eq 'Drivers'} | ForEach-Object { $wsus.DeleteUpdate($_.Id.UpdateID); Write-Host $_.Title removed }
6) Ran the Server cleanup wizard and it was finally able to run successfully.
7) Ran the following PowerShell script as Adminstrator to remove ALL Declined Update records as I found this after further research:
[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer();
$wsus.GetUpdates() | Where {$_.IsDeclined -eq $true} | ForEach-Object {$wsus.DeleteUpdate($_.Id.UpdateId.ToString()); Write-Host $_.Title removed }
At this point WSUS is much more responsive and still meets my need to deliver probably 95% of all updates to any Windows XP, Vista, 7, or 8 machine I put on my bench along with most common Microsoft add-ins like Office, Silverlight, Bing, Live, etc. (This WSUS box also serves my home personal machines on an "everyday" basis.)
-Mike Tanis
Medford, WI
