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:

\\.\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:

  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: 

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:

  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.

Comments  

# 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?
Reply | Reply with quote | Quote
# 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
Reply | Reply with quote | Quote
# 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
Reply | Reply with quote | Quote
# threeait 2016-01-11 09:30
Thanks for the info, Nick! :-) -Oli
Reply | Reply with quote | Quote
# 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?
Reply | Reply with quote | Quote
# 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
Reply | Reply with quote | Quote
# Nick 2016-01-28 01:18
My tblEventInstance is only 18 MB The whole database is 16 GB This script USE SUSDB GO exec spGetObsoleteUpdatesToCleanup showed 35185 obsolete updates I've been running this USE SUSDB DECLARE @var1 INT 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 OPEN WC FETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1) BEGIN SET @msg = 'Deleting ' + CONVERT(varchar(10), @var1) RAISERROR(@msg,0,1) WITH NOWAIT EXEC spDeleteUpdate @localUpdateID=@var1 FETCH NEXT FROM WC INTO @var1 END CLOSE WC DEALLOCATE WC --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 go 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. Name rows reserved data index_size unus ed tbXml 1835011 6002536 4890440 59672 1052424 t bPreComputedLocalizedProperty 929627 807224 601440 135208 70576 tbLocalizedProperty 929627 634792 5 75232 3520 56040 tbFile 428015 264296 262104 1376 816 tbDeadDeployment 796316 311008 250048 55688 5272 tbPrerequisiteDependency 3352610 362936 1424 64 133024 87448 tbDeployment 534722 480432 130000 210032 140400 tbMoreInfoURLForRevision 757282 13 8192 94456 21272 22464 tbDriver 306597 168096 789 92 71920 17184 tbRevisionLanguage 2173624 118992 57392 40896 20704 tbProperty 299684 104952 43104 23064 38784 tbFileOnServer 428015 65360 39888 233 12 2160 tbInstalledUpdateSufficientForPrerequisit e 1642946 94992 39176 29200 26616 tbLocalizedProp ertyForRevision 929590 50472 36008 400 14064 tbRe vision 299687 93112 25968 43728 23416 tbUpdate 24 4978 65336 25272 24928 15136 tbPrerequisite 10113 25 63376 22472 16160 24744 tbEventInstance 6507 3 6800 18864 6400 11536 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 Use SUSDB GO SET NOCOUNT ON; -- 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 SELECT f.object_id , index_id , avg_page_space_used_in_percent , avg_fragmentation_in_percent , record_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f WHERE (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) FROM @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) BEGIN FETCH NEXT FROM curIndexes INTO @objectid, @indexid, @density, @fragmentation, @numrows; IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name) , @schemaname = QUOTENAME(s.name) FROM sys.objects AS o INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END FROM sys.indexes WHERE 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)'; ELSE 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.'; END -- 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 USE SUSDB EXEC sp_updatestats GO SELECT name AS stats_name, STATS_DATE(object_id, stats_id) AS statistics_update_date FROM sys.stats ; GO But I still don't know if the MMC will time out after this is all done!
Reply | Reply with quote | Quote
# 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
Reply | Reply with quote | Quote
# 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
Reply | Reply with quote | Quote
# 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.
Reply | Reply with quote | Quote

Add comment

Security code
Refresh

Subscribe to our newsletter

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

Categories

Archives