How to clean up the WSUS server and clients when reaching the locally published category limit

Summary:

After importing locally published updates with too many distinct product or vendor names, client synchronization from the WSUS server will fail.

WSUS only provides APIs for importing and managing locally published updates, it does not provide UI. So this problem would only be encountered by customers using a tool that build on top of the WSUS APIs. Such tools include Configuration Manager 2007, System Center Essentials, and third party tools.

Cause:

When importing a catalog of 3rd party updates into WSUS, the server takes the product name and the vendor name elements and creates categories that get associated with each update. During Client/Server synchronization, ALL such locally published categories are sent to the client for evaluation. If too many distinct categories are published, it will cause the overall synchronization process to fail.

Solution:

There are several steps needed for the cleanup:
  1. Delete the update “metadata” from the server that reference the categories. When the last update the references a category is deleted, the category will be deleted as well.
  2. Unwedge clients that may have cached the existing categories. Until they are unwedged, they will not be able to synchronize from the server again.
  3. (optionally) Remove the update binaries from the server. Once the update metadata is removed, the update can no longer be used by the server. It is advisable to also clean up the update content as well since it takes up space on the hard disk unnecessarily.

Step 1 – Deleting update metadata

The scripts provided will delete all updates and their references to the catalog originally imported. They require PowerShell to be installed on the WSUS server machine. For details on how to get PowerShell, see the references section.
1 : Enumerate all local categories
# enum-local-categories.ps1
# Load administration
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.UpdateServices.Administration') | out-null


# Create update server
write-host "<<< Connecting to WSUS server >>>" -foregroundcolor "yellow"
$updateServer = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer()

# Get all categories
write-host "<<< Getting all categories >>>" -foregroundcolor "yellow"
$categories = $updateServer.GetUpdateCategories()

# Print out the local 'Company' categories
write-host "<<< Dumping locally published company categories >>>" -foregroundcolor "yellow"
write-host " Company categories "
write-host " ================== "
write-host ""
$i = 1
foreach ($category in $categories)
{
  if ($category.Type -eq [Microsoft.UpdateServices.Administration.UpdateCategoryType]::Company)
  {
    if ($category.UpdateSource -eq [Microsoft.UpdateServices.Administration.UpdateSource]::Other)
    {
      write-host "  Category #" $i
      write-host "  ------------ "
      write-host "   ID    = " $category.Id
      write-host "   Title = " $category.Title
      write-host "   #Subcategories = " $category.GetSubcategories().Count
      write-host "   #Updates = " $category.GetUpdates().Count
      write-host ""
      $i++
    }
  }
}

trap
{
	write-host "Error Occurred"
	write-host "Exception Message: " 
	write-host $_.Exception.Message
	write-host $_.Exception.StackTrace
	exit
}

# EOF


2: Delete all updates that belong to a given category:
# delete-update-in-category.ps1
# Load administration
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.UpdateServices.Administration')

# Create update server
write-host "<<< Connecting to WSUS server >>>" -foregroundcolor "yellow"
$updateServer = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer()

# Get all categories
write-host "<<< Getting all categories >>>" -foregroundcolor "yellow"
$categories = $updateServer.GetUpdateCategories()

# Delete updates belonging to a category
write-host "<<< Ready to delete all updates that belong to a category >>>" -foregroundcolor "yellow"
$categoryToDelete = read-host "Enter category Id"
write-host " All updates belonging to category " $categoryToDelete " will be deleted!"
$sure = read-host " Continue? (y/n)"

if ($sure -ne "y")
{
  exit
}

# Get all updates that belong to category
write-host ""
write-host "<<< Searching for updates belonging to category >>>" -foregroundcolor "yellow"
foreach ($category in $categories)
{
  if ($category.Id.ToString() -eq $categoryToDelete)
  {
    $updatesInCategory = $category.GetUpdates()
    break
  }
}

foreach ($update in $updatesInCategory)
{
  write-host "Deleting update " $update.Id.UpdateId "..."
  $updateServer.DeleteUpdate($update.Id.UpdateId)
  write-host " Done!"
}

write-host ""
write-host "All updates belonging to category " $category.Id.ToString() " are deleted"

trap
{
	write-host "Error Occurred"
	write-host "Exception Message: " 
	write-host $_.Exception.Message
	write-host $_.Exception.StackTrace
	exit
}

# EOF

Step 2 – Un-wedge clients

To unwedge clients that have enoucountered this problem, it is necessary to get them to purge their cached update metadata. One way to do this is to change the servers “ID”. If one is using the Windows Internal Database (which is the default for WSUS installation), this can be done as follows:
%programfiles%\Update Services\Setup\ExecuteSQL.exe -S %Computername%\MICROSOFT##SSEE -d "SUSDB" -Q 
"UPDATE dbo.tbConfigurationA SET ServerID = newid()"

If using another SQL database other than Windows Internal Database, the SQL command above should be run against the instance name <serverName>\instanceName where serverName is the name of the server and instanceName is the name of the SQL instance.

Step 3 – (Optionally) clean up content

  1. Run Cleanup Wizard to cleanup content files no longer referenced in the Server. Select “Unneeded update files” from the selections page.
  2. Use WsusUtil /ListUneededPackageDirectories to clean up the package folder as well.

References

WSUS Local Publishing
Windows PowerShell

Last edited Feb 11, 2009 at 9:11 PM by rhearn, version 1

Comments

HeyAdmin Jan 4, 2016 at 9:07 PM 
Does anyone know WHY there's a 100-category limit? Seems ridiculous to me.

troyaltman Oct 22, 2015 at 4:12 PM 
Thank You!!! I beat my head against the wall for ages and this is precisely what I was looking for.
Excellent work and thanks for sharing.

ryantaylor16 Jan 13, 2011 at 6:19 PM 
Got it all figured out. Run Powershell from an elevated command prompt and use the ID generated from the first script. I was attempting to use the Number Category displayed.

Thanks for this little tutorial. Saved me days of work.

ryantaylor16 Jan 13, 2011 at 1:49 AM 
I get the following: Any ideas?

<<< Searching for updates belonging to category >>>
Deleting update ...
Error Occurred
Exception Message:
Cannot convert null to type "System.Guid".
at System.Management.Automation.LanguagePrimitives.ConvertTo(Object valueToConvert, Type resultType, Boolean recursio
n, IFormatProvider formatProvider, TypeTable backupTypeTable)
at System.Management.Automation.Adapter.PropertySetAndMethodArgumentConvertTo(Object valueToConvert, Type resultType,
IFormatProvider formatProvider)
at System.Management.Automation.Adapter.MethodArgumentConvertTo(Object valueToConvert, Boolean isParameterByRef, Int3
2 parameterIndex, Type resultType, IFormatProvider formatProvider)
at System.Management.Automation.Adapter.SetNewArgument(String methodName, Object[] arguments, Object[] newArguments,
ParameterInformation parameter, Int32 index)