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

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