Click an Ad

If you find this blog helpful, please support me by clicking an ad!

Wednesday, July 9, 2014

Monthly WSUS Database ReIndexing - The Automated Way

A buddy of mine clued me in on this post that recommends re-indexing the WSUS database every month. Sounds like a good candidate for automation to me....

First off, the environment I'm operating under runs WSUS 3.0 SP2 on a Windows Server 2008 R2 64-bit box that is fully patched. WSUS utilizes the Windows Internal Database, and not a full blown SQL database.

The first thing you'll need is "sqlcmd". I tried just copying sqlcmd.exe from one of my SQL servers, but that didn't turn out so well. I was worried that I was going to have to install all of SSMS on my WSUS server, which wouldn't be horrible, but I like to keep things tidy and this seemed like overkill. Some research led me to this download page for the SQL 2005 Feature Pack.

  • Download the Microsoft SQL Server Native Client from that download page (skip the red "Download" button at the top for ala carte offerings below; more rejoicing).
  • Scroll down farther and also grab the appropriate Microsoft SQL Server 2005 Command Line Query Utility.
  • On your WSUS server, install the Native Client, and then install the Command Line Query Utility. In my case, sqlcmd.exe was created in C:\Program Files\Microsoft SQL Server\90\Tools\binn.
  • Now, copy the WSUSDBMaintenance.sql script on Technet to a folder (I'll use C:\PS).
  • Create a new batch file in C:\PS containing the following:

cd "C:\Program Files\Microsoft SQL Server\90\Tools\binn"

sqlcmd -I -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -i C:\PS\WsusDBMaintenance.sql

  • Finally, schedule the task on the WSUS server so it runs once a month. I picked the last Sunday.

If you're familiar with the blog, you may wonder why I'm not using a Powershell script. :)
The answer is because this is the easiest way to implement this tiny operation. Why fool around with Invoke-Expression when a batch file works just fine?

When I ran this for the first time, I assumed it would take a long time, since I'd never done it before. To my surprise, the process took about 3 minutes.

No comments:

Post a Comment