WSUS Database Maintenance for SQL Express

To perform database maintenance (defragmentation, re-indexing) on a WSUS database that is installed using SQL Express, I use the following solution.  Because SQL Express has no SQL Server Agent, you cannot create jobs or maintenance plans, but we can use Powershell with a Scheduled Task to perform regular maintenance.  I am using SQL Express 2012.

Download the WSUSDBMaintenance script from the Technet Gallery, and save it to a local folder on your WSUS server/s.

Then use the following Powershell script as a scheduled task to call the sql maintenance script and send an email to yourself with the output of the sql script as a log file.

$WSUSServers = @(
    "wsussrv-01v",
    "wsussrv-02v",
    "wsussrv-03v",
    "wsussrv-04",
    "wsussrv-05v",
    "wsussrv-06v",
    "wsussrv-07v"
    "wsussrv-08",
    "wsussrv-09v",
    "wsussrv-10v",
    "wsussrv-11v"
    )

foreach ($server in $WSUSServers)
    {
        $S = New-PSSession -ComputerName $server
        Invoke-Command -Session $S -ArgumentList $server -ScriptBlock {
        param ($server)
        Invoke-SQLCmd -InputFile C:\LocalScripts\SUSDB_Maintenance.sql -ServerInstance $env:COMPUTERNAME -OutputSqlErrors $True -Verbose *>$env:TEMP\SUSDB_Maintenance_$Server.log
        Send-MailMessage -To Trevor.jones@contoso.com -From Powershell@contoso.com -SmtpServer mysmtpserver -Subject "WSUS DB Maintenance log for $server" -Attachments $env:TEMP\SUSDB_Maintenance_$Server.log
        }
        Remove-PSSession $S
    }

Note that I am performing this maintenance on multiple WSUS servers in succession using an array.

In the Invoke-SQLCmd command, change the inputfile to the location of your sql script.

In the Send-MailMessage command, use the particulars for your environment

Powershell remoting must be available as the command will be run on the WSUS server itself.

If you are using an older version of SQL Express, you may need to add the SQL snapins first as described here.  In SQL Express 2012, the cmdlet is a part of a module that will be imported when you call it (Powershell 3.0 +).