3aIT Blog


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:


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:

  1. Click on the database name 'SUSDB'
  2. Click 'New Query'
  3. Type 'truncate table tbEventInstance' in to the Query Editor
  4. 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: 


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:

  1. Right click on the database name ("SUSDB") on the left
  2. From 'Tasks', select 'Shrink'=>'Files'
  3. 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.
  4. 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)
  5. 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.

# Ward Rogers 2015-01-22 16:12
"Shrink file to" is reporting a minimum of 19177 MB from total size 19184 MB. Any thoughts on how to get it to shrink more than just a few MB?
# threeait 2015-01-22 16:25
How do. Yes, that doesn't sound like much of a shrink, does it. Did you have any problems opening the database and cleaning out the log tables (Step 4 onwards, above)? "Shrink" will only defrag the data and free any holes that are left. To get any major shrinkage, you first need to remove some actual (redundant) data. In the blog above, I only describe my experiences removing obviously redundant "event" (log) data. There may be some other good candidates to delete or truncate. If you find any, please let me know :-) -Oli
# Nick 2015-12-22 23:19
Another way to get more breathing room is via the bad things that happen to tempdb. On my unit it lives at c:\windows\sysmsi\ssee\msmsql.2005\mssql\data. Stop the MSSQL$MICROSOFT##SSEE service. Navigate to the appropriate folder. Rename the tempdb.LDF file to tempdb.ldf.bad Start the service. A new tempdb.ldf file will be created and tempdb.mdf will also shrink dramatically. This freed up nearly a GB for me
# threeait 2016-01-11 09:30
Thanks for the info, Nick! :-) -Oli
# James Newton 2016-01-04 22:36
In my system, the tbEventInstance table is only 1.3 MB and has 583 rows... dropping it isn't going to save much, I think... still, the SUSDB.mdf file is 15GB. Any idea where the bloat is coming from?
# threeait 2016-01-11 09:29
Hi James, I couldn't say off-hand. But if you have successfully opened the database in Enterprise Manager, have a google for SQL that will tell you the size of each table. Once you have identified the offenders, you'll need to make a call about what to do about it - is it full of data that can be removed (as I found), or is it necessary data which is definitely needed for WSUS operation? If the latter, there is probably little you can do. Google for "moving wsus database" and put it on a bigger disk. If it is the former, then truncate the tables. Make good backups first! Thanks, -Oli
# Nick 2016-01-28 01:18
My tblEventInstance is only 18 MB
The whole database is 16 GB

This script

exec spGetObsoleteUpdatesToCleanup

showed 35185 obsolete updates

I've been running this

DECLARE @msg nvarchar(100)
Declare @myTable table (Col1 INT)

--CREATE TABLE #results (Col1 INT) INSERT INTO #results(Col1)
INSERT INTO @myTable (Col1)
EXEC spGetObsoleteUpdatesToCleanup
--DECLARE WC Cursor FOR SELECT Col1 FROM #results order by Col1
DECLARE WC Cursor FOR SELECT Col1 FROM @myTable order by Col1
BEGIN SET @msg = 'Deleting ' + CONVERT(varchar(10), @var1) RAISERROR(@msg,0,1) WITH NOWAIT
EXEC spDeleteUpdate @[email protected]
--DROP TABLE #results

For 10 days now.
It doesn't bag the server.
It kills one obsolete update per minute or so.
I stop the query, and close the script, and it says there are transactions to complete -- which I let it do.
Then I start the script again.

tblXML is the huge one.
But it is down from 12 GB to 4.5 GB with 6000 updates yet to kill
Here are the biggies (sorry for mat issues!)
Obtained with

use susdb

declare @tablesize table (
[name] nvarchar(256),
[rows] varchar(18),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)

insert @tablesize exec sp_msForEachTable 'exec sp_spaceused [?]'
select * from @tablesize order by data desc

and sorted properly (text you know!) in Excel

tblXML is stiil the largest, but coming down.


After the killing spree completes, I think the indexes and statistics will need updating.

I use this variation on the WSUS maintenance script to just LIST what indexes need work


-- Rebuild or reorganize indexes based on their fragmentation levels

DECLARE @work_to_do TABLE (
objectid int
, indexid int
, pagedensity float
, fragmentation float
, numrows int

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @numrows int
DECLARE @density float;
DECLARE @fragmentation float;
DECLARE @command nvarchar(4000);
DECLARE @fillfactorset bit
DECLARE @numpages int

-- Select indexes that need to be defragmented based on the following
-- * Page density is low
-- * External fragmentation is high in relation to index size
PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121)
INSERT @work_to_do
, index_id
, avg_page_space_used_in_percent
, avg_fragmentation_in_percent
, record_count
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f
(f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count 50 and f.avg_fragmentation_in_percent > 15.0)
or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)

PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20))

PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)

SELECT @numpages = sum(ps.used_page_count)
@work_to_do AS fi
INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id
INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

-- Declare the cursor for the list of indexes to be processed.
DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do

-- Open the cursor.
OPEN curIndexes

-- Loop through the indexes
WHILE (1=1)
INTO @objectid, @indexid, @density, @fragmentation, @numrows;

@objectname = QUOTENAME(o.name)
, @schemaname = QUOTENAME(s.name)
sys.objects AS o
INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
o.object_id = @objectid;

@indexname = QUOTENAME(name)
, @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END
object_id = @objectid AND index_id = @indexid;

IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation = 5000 AND @fillfactorset = 0
Print N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';
Print N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
--PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command;
--EXEC (@command);
--PRINT convert(nvarchar, getdate(), 121) + N' Done.';

-- Close and deallocate the cursor.
CLOSE curIndexes;
DEALLOCATE curIndexes;

I then copy-and-paste the suggested commands in a few at a time until all the indexes have been maintenance.

Then it's updating stats time

EXEC sp_updatestats
SELECT name AS stats_name,
STATS_DATE(object_id, stats_id) AS statistics_update_date
FROM sys.stats ;

But I still don't know if the MMC will time out after this is all done!
# Lee Willoughby 2016-03-03 12:55
Nick, What you've described it exactly my issue at present!! I started with 24,000 in the unneeded updates and have now cleared around 5,000 - so still a lot to go! I don't however see much change in the size of the database. Updates are taking a lot longer than you have described to delete so it's a bit tedious. Do you have any update on your progress and whether the overall process fixed your timeout errors? Thanks, Lee
# Benjamin Botts 2016-06-02 17:14
"WHERE (f.avg_page_space_used_in_percent 15.0) or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)" I'm assuming the missing comparison operator in "page_count 50" is >. Please correct me if I'm wrong. Thank you, Ben
# Nick 2017-03-16 16:53
Update: I noticed that the drive (1 TB) that I had the WSUS content on was approaching full, and the database was approaching 16 GB. The consoles were forever timing out. I tried instructions for whacking the content and then running 'wsusutil reset.' Forget it. The WSUS never downloaded content again. Tore it out, reinstalled it. Took four cracks to synchronize. Database is now 6 GB and content down from 810 GB (!!!) to 2.65 GB Console works again. My advice: If you have successfully installed WSUS 3.0 SP2 and had it running, uninstall/reinstall is the most time efficient way to successfully control its insane use of drive space, rather than attempting maintenance.
# Stephen 2018-10-18 11:18
Truncating the "tbXml" table did the trick for me. That table was taking up 13GB of space within SUSDB. You can locate the largest tables by running a "Disk Usage by Top Tables" report within SSMS. ALWAYS BACK UP your database before making any changes!!

New comments disabled.

Subscribe to our newsletter

Sign up to receive a monthly roundup of our best blogs and latest news.
Fields marked with * are required