Keeping Windows Server Update Services (WSUS) Tidy

Keeping WSUS running at it’s best requires more effort than is commonly understood and without proper maintenance you might eventually start seeing nasty errors from the console.

wsus_errors

I’ll try to run through a few of the solutions I’ve discovered and how you can use them together to keep things running smoothly. You can also use these processes to keep WSUS managed by ConfigMgr tidy 🙂

Categories and Products

An important first step to take, is to only pick the categories and products that apply to your estate, before running your initial sync. Then occasionally it’s worth revisiting the list to ensure it is still relevant. Getting this right will save you headaches later on.

WSUS Database

The second thing to consider is re-indexing and de-fragmenting the database. Available on the MS TechNet Gallery, is a SQL script which does this job perfectly. However, running this script can be a bit tricky if you accepted the default ‘WID’ (Windows Internal Database) option during setup.

  1. Download SQL Management Studio – here
  2. Download the maintenance script – here
  3. Determine whether your WSUS database is WID or SQL:
    • Server 2008 R2 – Open Server Manager then on the right column select ‘Windows Server Update Services’ and then view services. If ‘Windows Internal Database (MICROSOFT##SSEE)’ is present, the instance is using WID.
    • Server 2012 R2 – Open Server Manager, then on the right column select ‘WSUS’ and then scroll right down to the bottom to see the roles and features installed. If you see ‘WID Database’ instead of just ‘Database’, your instance is using WID.
  4. If you are running SQL skip to step 8.
  5. If you are running WID, you will need to install the SQL Management Studio, downloaded earlier, on the server running WSUS.
  6. Now you will need to logon to the server as the builtin administrator account, or the account that installed WSUS.
  7. Open SQL Management Studio and connect to one of the following:
    • Server 2008 R2 – \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
    • Server 2012 R2 – \\.\pipe\MICROSOFT##WID\tsql\query
  8. Once connected, navigate to the SUSDB database an run a new query.
  9. Paste the code from the downloaded maintenance script into the new query window and run the query.

This script can be ran as a monthly task using a SQL job or if you are using WID, a batch cmd that calls SQLCMD, using task scheduler. I’ll look into the exact command you need later.

Decline Superseded Updates

If you do not regularly decline superseded updates, over time disk space on the server will quickly use up and the console will become unresponsive. You can of course manually go through the console and decline superseded updates, but why would you do that if PowerShell can do it for you? In a previous post, I looked into a script which connects to the WSUS API and declines superseded updates. I’ve improved the script slightly, adopting some ideas from a script published on the MS Technet Gallery and have uploaded it to GitHub.

Again, this can be run on a regular basis using as a scheduled task.

Troubleshooting

An issue I came across while going through this process for a client, was that the API call kept timing out. This was because they had left their WSUS environment running for years without maintenance. I tried for a long while to figure out how I could extend the time out period etc but couldn’t come up with anything. I tested the below process for getting round this successfully:

  • Ensure you have a backup of the SUSDB then run a re-index.
  • When that completes, run the following stored procedure in SQL Server Management Studio or SQL Server Management Studio Express.

DECLARE @var1 INT
DECLARE @msg nvarchar(100)
CREATE TABLE #results (Col1 INT)
INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup
DECLARE WC Cursor
FOR
SELECT Col1 FROM #results
OPEN WC
FETCH NEXT FROM WC
INTO @var1
WHILE (@@FETCH_STATUS > -1)
BEGIN SET @msg = ‘Deleting’ + CONVERT(varchar(10), @var1)
RAISERROR(@msg,0,1) WITH NOWAIT EXEC spDeleteUpdate @localUpdateID=@var1
FETCH NEXT FROM WC INTO @var1 END
CLOSE WC
DEALLOCATE WC
DROP TABLE #results

This will run stored procedures called spGetObsoleteUpdates and spDeleteUpdate which remove unused updates and update revisions. After that finishes, try running the maintenance script again. If that fails, unfortunately you are left with reinstalling WSUS with a fresh database.

WSUS Cleanup

The following script performs the tasks completed by the inbuilt wizard and is maintained here:

[void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer();
$cleanupScope = new-object Microsoft.UpdateServices.Administration.CleanupScope;
$cleanupScope.DeclineSupersededUpdates = $true
$cleanupScope.DeclineExpiredUpdates = $true
$cleanupScope.CleanupObsoleteUpdates = $true
$cleanupScope.CompressUpdates = $true
$cleanupScope.CleanupUnneededContentFiles = $true
$cleanupManager = $wsus.GetCleanupManager();
$cleanupManager.PerformCleanup($cleanupScope);

Putting it all together

To automate the solution using task scheduler, create a folder such as C:\WSUSMaint and copy the two powershell scripts and sql scripts to that location. Then in task scheduler, create three tasks with the following actions:

  • WSUS Cleanup action – powershell -file C:\WSUSMaint\wsus_clean.ps1
  • SUSDB Re-index action – “C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.exe” -S \\.\pipe\Microsoft##WID\tsql\query -i C:\WSUSMaint\SUSDBMaint.sql
    • In the above sql script substitute “\\.\pipe\Microsoft##WID\tsql\query” with “\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query” if you are on Server 2008r2
  • WSUS Decline action – powershell -file C:\WSUSMaint\wsus_decline.ps1

It is recommended that you run these when there is no chance of a collide with a WSUS sync as this can cause problems where it will re-sync the updates you are trying to clear. Here is an example schedule for your three tasks:

  • Run wsus_Decline task
  • Wait 1 hour
  • Run wsus_Clean task
  • Wait 1 hour
  • Run SUSBMaint.sql task

I hope this all makes sense and if you have a question, leave a comment.

Advertisements
Keeping Windows Server Update Services (WSUS) Tidy

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