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 = @(

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 -From -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 +).

Leave a Reply

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.