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.
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.
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!