Wednesday, September 3, 2014

Reindex and Shrink a WSUS Database on 2008R2


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!








Coolest nerd tech ever:

 http://amzn.to/2BFbtM5



5 comments:

  1. Thank you very much. This was helped me a lot.

    ReplyDelete
  2. After many google searches this is finally what allowed me to re-index the database and allow cleanup to run without errors.

    ReplyDelete
  3. good stuff here too: https://pastebin.com/u2yuexXf

    ReplyDelete
  4. and here: https://www.reddit.com/r/sysadmin/comments/49u3p3/wsus_server_2012_data_drive_is_full_400gb_how_to/

    ReplyDelete

Comments, Suggestions or "Thank you's" Invited! If you have used this info in any way, please comment below and link/link-back to your project (if applicable). Please Share.
I accept Bitcoin tips of ANY amount to: 1GS3XWJCTWU7fnM4vfzerrVAxmnMFnhysL
I accept Litecoin tips of ANY amount to: LTBvVxRdv2Lz9T41UzqNrAVVNw4wz3kKYk