- 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.)Now for the content...
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"
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!