Wednesday, September 3, 2014

Reindex and Shrink a WSUS Database on 2008R2



I have a basic local WSUS install on Windows 2008R2 Server of which the SUSDB has grown significantly. Below is the requirements and functions I found to re-index and shrink the DB.

You may wish to run the WSUS Server Cleanup Wizard prior to these step.  I often found that running the "Unused updates and update revisions" separately from the others is a good idea. I.E. i often select the 2nd,3rd,4th, and 5th options, run it, then re-run it with only the 1st option selected.

Does "Deleting unused updates" get stuck?  Try http://wininfra.net/2016/05/13/workaround-for-wsus-sql-timeout-errors/ (I had to do this once and it took days, but fixed the issue.)

Now for the content...

1) How to re-index:

The re-index script was found at http://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61

First download and install sqlncli_x64.msi and SQLServer2005_SQLCMD_x64.msi from http://www.microsoft.com/en-us/download/details.aspx?id=15748

Then copy/paste the script mentioned above into a new file named WsusDBMaintenance.sql .

Open a command prompt and change to the folder where you saved the script and execute the following command:
sqlcmd -I -i"WsusDBMaintenance.sql" -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query > reindex.txt

Of course you can create a batch file with the same command in it.

This may take some time, and results will be piped into reindex.txt for your review.


2) How to shrink:

We will use the SQL 2005 Express version of "Microsoft SQL Server Management Studio".  Download and install SQLServer2005_SSMSEE_x64.msi from http://www.microsoft.com/en-us/download/details.aspx?id=8961

Open SSMSE and connect to the local database by server name: \\.\pipe\mssql$microsoft##ssee\sql\query

Locate the database named SUSDB, right click, select Tasks>Shrink>Database>OK.

This may take a significant amount of time.

My database shrunk from 16.1GB to 12.9GB.

For further disk space recovery, try my previous post http://steronius.blogspot.com/2014/05/cleanmgr-on-windows-2008-r2-active.html which reclaimed 6+GB on my WSUS server.

---