System Requirements:
- Windows Software Update Services 3.0
- Windows Server 2008, 2008 R2
The Problem:
Microsoft recommend that you perform monthly maintenance on your WSUS database to keep it in good order. The following pages represent the instruction for undertaking this activity
- Windows Server Update Services 3.0 SP1 Operations Guide Appendix I: Database Maintenance
- Re-index the WSUS 3.0 Database
This document simply seeks to clarify the process when using Windows Internal Database (instead of SQL Server) under Windows Server 2008/R2.
The Fix
The following summarises the steps involved in the process.
- Install the SQL Server Native Client for your processor on the WSUS server
- Install the SQLCmd utility for your processor on the WSUS server
- Copy & Paste the T-SQL code from Re-index the WSUS 3.0 Database into a notepad file and save it on c:\ as WsusDBMaintenance.sql (or download here)
If you wish to automate or schedule the task:
- Create a .cmd file on your desktop
- Enter the following into it
"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd.exe" -I -i"c:\WsusDbMaintenance.sql" -S "np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query"- Note that the version of the SQL Server SQLCmd that you install will need to be reflected in the version number (90 above). 90 = SQL Server 2005, 100 = SQL Server 2008 and so on.
- Right click the .cmd file and select “Run as Administrator” to launch it through an elevated command prompt
- If you setup a scheduled task for this, remember to set the always run with full permissions option.
If you just want to run it once:
- Open an elevated command prompt
- Enter the command:
"c:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd.exe" -I -i"c:\WsusDbMaintenance.sql" -S "np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query"- Note that the version of the SQL Server SQLCmd that you install will need to be reflected in the version number (90 above). 90 = SQL Server 2005, 100 = SQL Server 2008 and so on.