WSUS is both a very useful beast and a wild one: It allows you to manage a huge number of Windows workstations to keep your PCs updated and your network secure, yet it itself can be quite hard to manage.
It has built in clean-up tools for removing old, obsolete or superseded patches, but nothing to help you manage the database itself, which can grow to quite an alarming size.
This is how to properly clean up a WSUS server that is critically out of space, and reduce its database size by almost 90%.
1. See if you can reclaim some space using conventional means
The first you may know that your server is in trouble is that it only has a few meg free and some services are failing. WSUS, by default, places its database and downloaded files on your system drive, which can leave your server in a critical state.
You can use a tool like CCleaner to remove temporary files and old server system updates to give yourself some breathing space.
2. Use the WSUS Cleanup Tool to remove updates that are no longer needed
Under "Options" in the WSUS admin tool, you can find the cleanup wizard. This does not necessarily remove disk files you would expect and can need some encouragement by manually "unapproving" updates under "All Updates". Follow the steps here to deselect updates you don't ever want and remove all updates from disk. By removing all updates from disk and running a reset, it will start downloading those updates again; this delete is nothing to worry about.
It would be best to stop the WSUS service after this step.
3. Stop the WSUS service
The service is stopped via the IIS Manager, as it runs as a web site.
4. Taming the database - installing the right tools
By this point, you will hopefully have recovered a few gig of disk space. In my case, I still had the problem of the WSUS Database itself being 30Gb. This was after a couple of years of use with maybe 50 PCs - so a fairly small site.
The database in WSUS is an embedded SQL Server database (SQL Server Express). It can be managed via SQL Server Management Studio. You can download this for free from here.
Note: There are many versions of Management Studio. After many problems downloading versions that couldn't be installed on this particular server due to its particular configuration, version, patch level etc, I found Management Studio 2005 worked a treat. Your mileage may vary and it is worth persevering until you find an appropriate working version.
5. Taming the database - connecting to the .mdb file
You can now connect to the WSUS .mdb file by opening SQL Server Management Studio and entering in to the "Server" box:
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
Use "Windows Authentication".
If this goes well, you should be looking at a standard database view, with a list of tables on the left and an info window on the right.
You are unlikely to be able to simply shrink the database and recover any space this way. First we need to free up some space by deleting redundant data.
6. Deleting redundant data
WSUS logs everything it does. Over time, this can eat a lot of space in the database with surprisingly few workstations.
The logs live in the table "tbEventInstance".
Delete all of these like this:
- Click on the database name 'SUSDB'
- Click 'New Query'
- Type 'truncate table tbEventInstance' in to the Query Editor
- Click the 'Execute' button - or press F5.
This removed 90% of the data in the database (several million rows) for me and did not affect WSUS operation or administration, apart from to speed up certain administration views which used old event data.
If you are concerned, and have the means, it would be prudent to back this file up first. 3aIT can accept no liability for loss or damage to your systems resulting from not taking the appropriate precautions. To put that another way: This worked for me, your mileage may vary.
The file itself defaults to living here:
C:\WSUS\Database\UpdateServicesDbFiles
If you're less cavalier than me, and care about reporting on recent activity, you can substitute a "delete from ... where" for the "truncate" above, and delete only messages older than a week or two.
7. Shrink the DB file
This is a slow step which you may want to run overnight. Where data has been deleted from the database, this will not be reflected in the file size until the file is shrunk. It is now effectively full of holes and needs defragging.
Do this:
- Right click on the database name ("SUSDB") on the left
- From 'Tasks', select 'Shrink'=>'Files'
- In the wizard, change the "Shrink Action" from "Release Unused Space" to "Reorganise pages before releasing unused space. This takes the holes out of the file.
- Change the number in the "Shrink File To" box to match the number it tells you the minimum can be. In my case, it told me it could shrink it to 2Gb (from just over 30Gb)
- Click "OK" and wait for a very long time
8. Restart WSUS and check all is well
The next day, you should find you have a file on disk that is approximately the same size you told Management Studio to shrink it to.
Restart WSUS via the IIS Manager. Check you can connect to it from the WSUS admin tool. Run any updates and do any approvals, then run the "reset" command again from here to persuade it to re-download any updates it needs.
If you think the database is going to grow out of hand again quite quickly, consider moving it to somewhere better than your system drive. Here are some reasonable instructions on how to do this using the sqlcmd.exe commandline tool to detach and reattach a database. If you don't have this installed, you can run the SQL commands through Management Studio, or use "Detach" / "Reattach" from the "Tasks" context menu.