Using the WSUS API and PowerShell to Perform Maintenance on the SUSDB Database

In my previous article, I showed how you can utilize the Windows Server Update Services (WSUS) API to connect to the SUSDB database that is being used by the WSUS server to perform T-SQL queries against the database for information.

As cool as that is by itself, I am going to take this a step further by utilizing an existing T-SQL script that is used to re-index the SUSDB database so it can be done without the need of SQL software such as osql.exe, sqlcmd.exe or the SQL Server Management Studio (SSMS).

The function that I wrote is called Invoke-WSUSDBMaintenance and is available to download from the Technet Script Repository. This script utilizes the same techniques that I showed in my previous article with the exception of a new method called ExecuteCommandNoResult() which executes the script and doesn’t return any results. I did remove a couple of things from the existing T-SQL code such as the use of PRINT (I don’t really care what is being displayed and it wouldn’t be displayed back to you anyways, I checked) and GO (this is used to execute the commands in sqlcmd.exe and osql.exe and will throw errors if used here).

I have the –WhatIf just in case you wanted to verify that it will work and it has the usual support for –Verbose as well. I did take a different route when loading the assembly for management of the APIs by loading the assembly at the verification of the Update Server name and will throw and error and cause the function to fail if the assembly doesn’t exist.

[ValidateScript({
    If (-Not (Get-Module -List -Name UpdateServices)) {
        Try {
            Add-Type -Path "$Env:ProgramFiles\Update Services\Api\Microsoft.UpdateServices.Administration.dll"            
            $True
        } Catch {
            Throw ("Missing the required assemblies to use the WSUS API from {0}" -f "$Env:ProgramFiles\Update Services\Api")
        }
    } Else {$True}
})]

The only required parameters are for the UpdateServer and the Port that the server is using for communication. From there on out it does its own thing to locate the database and begin the maintenance such as re-indexing the database and updating the statistics.

Invoke-WSUSDBMaintenance -UpdateServer DC1 -Port 80 -Verbose

image

Time of completion will vary depending on how your server is being taxed of course, but it will complete and your SUSDB database should be running better than before! As always, feel free to let me know what you think of this function!

Download

Script Repository

About Boe Prox

Microsoft Cloud and Datacenter MVP working as a SQL DBA.
This entry was posted in powershell, WSUS and tagged , , , . Bookmark the permalink.

4 Responses to Using the WSUS API and PowerShell to Perform Maintenance on the SUSDB Database

  1. polishpaul says:

    Do you know how to use PowerShell to backup the Windows Internal Database?

  2. polishpaul says:

    Do you know of a way to use PowerShell to backup the Windows Internal Database?

  3. eman says:

    Thank you, most useful (especially when WSUSCleanupCL doesn’t seem to run on S2012)!

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s