Reindex and Shrink a WSUS Database on 2008R2

UPDATE Feb 2022: This is obsolete. Time to upgrade to Win 2019 or newer and use these resources:
- https://github.com/samersultan/wsus-cleanup
   Which needs https://docs.microsoft.com/en-us/sql/connect/odbc/windows/release-notes-odbc-sql-server-windows?view=sql-server-ver15#178
   and https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-2017
   When scheduling it, be sure to "start in" the folder of the stored the files.
- MS best practices here: https://docs.microsoft.com/en-us/troubleshoot/mem/configmgr/windows-server-update-services-best-practices
- Further reading that i did not necessarily apply: https://www.deploymentresearch.com/fixing-wsus-when-the-best-defense-is-a-good-offense/



UPDATE Feb 2018: For Windows 2012 :
- Try this: https://community.spiceworks.com/how_to/103094-automate-wsus-cleanup
- You will need x64\msodbcsql.msi and x64\MsSqlCmdLnUtils.msi

___ Original Post for Windows 2008 R2 WSUS _________________________________________

I have a basic local WSUS install on Windows 2008 R2 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.)
   Edit: Another copy of this script: https://gist.github.com/Chris-ZA/efe09d076fabb62153ca247d834bb5b2 to be used similarly to below.
   i.e. for 2008R2: sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -i ".\wsusDBmaintenance.sql"
   or for 2012: sqlcmd -S \\.\pipe\MICROSOFT##WID\tsql\query -i ".\wsusDBmaintenance.sql"

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.

~~~
As always, good luck!


Please consider crypto tipping: