How This Article Came To Be
The contents of this article are the results of a long path of investigation, trial and error, and (bloody-minded) determination to find a way to clean up our corporate WSUS (Windows Server Update Services) installation. What I’ve documented below is partly based on work done by others (which I’ll provide attribution for as they are definitely due the credit), and other parts are based on my own personal investigations.
There have been several updates to this post since I first wrote it in late October 2018, based on findings of other readers using it for newer versions of WSUS. Thank you to Vladimir, Ulrich, and James for your helpful feedback!
Note that this article assumes you have some familiarity with SQL Server and its management studio (SSMS), as well as with general server management, like starting and stopping services.
As many other companies do, the one I work for employs WSUS to manage the Windows updates which are delivered to our various servers and workstations. I set this up back in mid-2015 to mitigate the fact that 50+ servers and workstations were hammering our 10Mbps corporate internet connection once a month to grab the updates directly from Microsoft. Given that a large part of our business relies on connecting to customer installations remotely, having little to no bandwidth available during a business day is not desirable. It took a few days to set up WSUS, and a few evenings for it to pull down all of the latest updates. With network policies in place to make workstations instead grab updates from the internal server, all was good. At least for the first while…
A year or so in I noticed that updates didn’t seem to be happening on a regular schedule anymore. I took a deeper look at the WSUS machine I’d set up and found that the 500GB drive the updates were stored on was full to the brim. I hastily cloned it over to a 1TB drive (providing about 800GB of total space on the update partition), expanded the partition to use the extra space, and synced down the latest updates. All was good. Until I had to do it again in less than a year, this time increasing the drive to a 2TB unit. I thought that would hold us for a while.
Fast forward a couple of years. That 2TB drive now had about 10GB free, and the database housing the WSUS data had grown to nearly 40GB. I researched things and shifted the database to the OS partition through a detach-move-attach cycle. I had to first install a copy of SSMS (SQL Server Management Studio) on the server to be able to interact with the Windows Internal Database instance. In the end, it was a simple enough process, and that solution is well documented on the internet. That database move freed up enough disk space to stop the low disk space warnings, but only just barely. The database was still 40GB and the update files were still consuming almost all of the partition’s 1.6TB of disk space.
So, How Did Things Get This Bad?
So, in between other tasks, I started researching whether or not the system could be cleaned up. It became very clear early on in my searches that this was not an uncommon problem, and was caused by a general “set it and forget it” mentality around WSUS management. Like so many other systems, maintenance of the system can be automated, but by default is not. And so the system runs, accumulating more data about available updates in the database, and more update files on the file system, eating up disk space while the performance of the database degrades.
In a perfect world, dutiful administrators run the cleanup tasks in the WSUS console every few weeks or so. These tasks clear out old, superseded updates from the database and their associated update files. However, if this kind of maintenance hasn’t been done in a few years, the WSUS console winds up timing out when it is instructed to do this, as the queries take too long to run. In addition, the indexing in the database becomes horribly inefficient, further slowing things down. One of the early indicators of this problem is the frequent display of an error when bringing up the WSUS console:
There Must Be A Way To Clean Up This Mess
Thankfully, there are many people out there who have worked out various parts of the solution, and I’ve found a few reliable ways to do some other cleanup. The general process follows this list of tasks:
- Install SSMS on the WSUS server if it isn’t already there. You’ll need this for running various scripts to clean up the WSUS database.
- Stop a few services on the WSUS server so that the following steps don’t prevent various maintenance steps from working.
- Take a backup of the WSUS database in case something goes horribly wrong during the cleanup.
- Run a script to rebuild/optimize the indexes in the WSUS database, which will make the other steps run faster.
- Run a WSUS stored procedure to determine how many obsolete updates are recorded in the database.
- Run a script to remove a batch of those updates from the database.
- Repeat steps 4 through 6 until there are no more updates to be removed. This may take several days depending on the volume of updates to be removed, though it speeds up as the number of updates in the database is reduced.
- Shrink the database files to reclaim space, and then rebuild/optimize the indexes.
- Using a copy of the WSUS database and a list of update files on the WSUS server, determine what files can safely be deleted, and then delete them.
1. Installing SSMS
This is probably the easiest part of the whole process. If WSUS is deployed on a full SQL Server instance instead of a Windows Internal Database, then the server may already have SSMS installed. Otherwise, it’s a matter of picking the right version. For my own purposes, I was working with Windows Server 2008 R2 and the WSUS version that came with it. I installed it with the Windows Internal Database, as the server’s sole purpose was to support WSUS. As such, I needed to install SSMS for SQL Server 2008 R2. It’s easy enough to find online. What you may need will vary by your own installation, so I leave it up to you to find on your own.
Connecting to the WSUS database through the Windows Internal Database is a bit different than doing so with a standard SQL Server instance. As well, you generally can’t access the instance from anywhere else but the WSUS server, hence needing to install SSMS on the WSUS server. The connection string I use in the SSMS connection prompt for the WSUS server is:
The connection is done using the named pipes protocol, pointing to the local machine, and employs Windows Authentication to get to the database. You need to be a local admin or a WSUS admin to do this. However, if you’re connecting to a true SQL Server instance, you can point at the machine’s instance in a more standard way. Either way, once you’re connected, you can explore the database objects and run queries in the normal manner.
2. Stopping Services
If your WSUS server is anything like mine, you’ll find that it is frequently running at the limits of its available memory, which slows down the queries. In order to free up memory, a few services can be temporarily stopped on the WSUS server. In my case, it dropped from consuming nearly 6GB of memory down to 1GB. Note that stopping these services will prevent WSUS from picking up new updates and block workstations from querying it for new updates, so you likely want to do this outside of normal business hours if you’re in a corporate environment.
The services are:
- IIS Admin Service
- World Wide Web Publishing Service
- Update Services
They may be named slightly differently on newer operating systems.
In addition, you can restart the database instance service, which for the Windows Internal Database is named:
- Windows Internal Database (MICROSOFT##SSEE)
For a standard SQL Server instance, it is often named something like:
- SQL Server ([instance name])
where [instance name] reflects what you named the instance, or is the default instance name, which is based on what version of SQL Server is installed.
3. Backup the WSUS Database
Since there is a real potential that the cleanup process could damage the WSUS database, I highly recommend taking a backup of it. If you have the disk space, you can simply stop the SQL Server or Windows Internal Database service, copy the WSUS database files to somewhere else, and then restart the service. Otherwise, you should be able to use the normal backup methods for SQL Server to take a full backup. If you’re not sure where the files are, look at the properties of the database when logged into SSMS.
4. Rebuild the WSUS Database Indexes
The following script was sourced from Microsoft. Run this within SSMS while connected to the WSUS database. It may take 15 minutes or more to run, depending on the size of the WSUS database.
/****************************************************************************** This sample T-SQL script performs basic maintenance tasks on SUSDB 1. Identifies indexes that are fragmented and defragments them. For certain tables, a fill-factor is set in order to improve insert performance. Based on MSDN sample at http://msdn2.microsoft.com/en-us/library/ms188917.aspx and tailored for SUSDB requirements 2. Updates potentially out-of-date table statistics. ******************************************************************************/ 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 < page_count - 1) or (f.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 < 30.0) SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; ELSE IF @numrows >= 5000 AND @fillfactorset = 0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)'; ELSE SET @command = 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; IF EXISTS (SELECT * FROM @work_to_do) BEGIN PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20)) SELECT @numpages = @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 PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20)) END GO --Update all statistics PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121) go EXEC sp_updatestats go PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121) GO
5. Determine How Many Obsolete Updates Exist
Once the indexes are rebuilt, you need to find out how many updates need to be cleaned up. When I started, the following stored procedure call identified over 28,000 of them. Open a new query window in SSMS, copy/paste the following, and run it. It may take several minutes to run.
USE SUSDB GO EXEC spGetObsoleteUpdatesToCleanup
This stored procedure returns a listing of the updates that are marked as obsolete, which are ones that have been superseded by other, newer updates. Because those older updates are obsolete, they will never be deployed to servers and workstations, and as such are no longer needed.
6. Remove a Batch of Obsolete Updates
If you have a lot of obsolete updates to delete, you’ll want to do so in manageable chunks so that you can periodically have the WSUS server obtaining and providing new updates to computers like it should. The following script, which I sourced and reformatted from here, lets you do just that. You can adjust the size of a batch by changing the number assigned to the @batchSize variable near the start of the script. When run, the script deletes obsolete updates one at a time until either it runs out of updates or it reaches the batch size.
In my circumstances, when I first started using the script, it took slightly more than 60 seconds per update, meaning a batch of 2,000 updates would take about 36 hours to go through. The speed is dependent on the hardware resources it has available, so it may be faster or slower for you. I recommend running the script for a small batch of maybe 20 to start with, average the amount of time it takes for each delete to complete, and then based on that calculate how many updates you could delete in the time span you have available at night and on weekends. For example, at 65 seconds per update, during the 10 or so hours an office is likely closed overnight, you could process slightly more than 500 updates. As the time to delete individual updates decreases, you can increase the number of updates you process in that time span. You can easily determine this by looking at the output of the script, shown the Messages tab, where it lists the exact time down to the millisecond of when each delete started.
As of this writing, with my WSUS database cleaned up, updates delete within about 8 seconds each.
Here’s the script:
USE SUSDB DECLARE @updateID INT ,@currentItem INT ,@itemsToDelete INT ,@batchSize INT DECLARE @msg NVARCHAR(200) SELECT @currentItem = 1 ,@batchSize = 2000 CREATE TABLE #obsoleteUpdates (localUpdateID INT) INSERT INTO #obsoleteUpdates (localUpdateID) EXEC spGetObsoleteUpdatesToCleanup SET @itemsToDelete = (SELECT COUNT(1) FROM #obsoleteUpdates) DECLARE ObsoleteUpdates CURSOR FOR SELECT localUpdateID FROM #obsoleteUpdates OPEN ObsoleteUpdates FETCH NEXT FROM ObsoleteUpdates INTO @updateID WHILE (@@FETCH_STATUS > - 1) BEGIN SET @msg = cast(@currentItem AS VARCHAR(5)) + '/' + cast(@itemsToDelete AS VARCHAR(5)) + ': Deleting ' + CONVERT(VARCHAR(10), @updateID) + ' @ ' + replace(convert(VARCHAR(50), getdate(), 126), 'T', ' - ') RAISERROR (@msg, 0, 1) WITH NOWAIT EXEC spDeleteUpdate @localUpdateID = @updateID SET @currentItem = @currentItem + 1 IF @currentItem < (@batchSize + 1) FETCH NEXT FROM ObsoleteUpdates INTO @updateID END CLOSE ObsoleteUpdates DEALLOCATE ObsoleteUpdates DROP TABLE #obsoleteUpdates
7. Keep At It!
It may take a week or so of evenings and weekends to clear out all of the obsolete updates using the steps above, but it will eventually finish. This is the first half of the battle, though. The scripts above clean up only the WSUS database, and do nothing to remove the already downloaded Windows Update files stored on the server.
8. Shrink the WSUS Database Files
This isn’t absolutely necessary, but if your WSUS database has grown to a huge size, you can shrink it down. There are a multitude of articles out there (like this one) that describe how to do this, so I won’t provide steps here. As a point of common sense, back up the WSUS database first. That said, once you shrink it, make sure you go back and run the script from step 4 above to rebuild all the indexes, as the shrink process makes them inefficient again.
9. Get Rid of the Obsolete Update Files
This was, frankly, the hardest part to figure out. A lot of the discussion I saw online about this part of the issue generally came to the conclusion of “reset WSUS and start again.” This essentially requires you to record the configuration of your WSUS installation, issue a command that wipes the databases and the file store, re-enter all of the configuration, and then have the server download the latest updates. In some ways, this approach is quicker than the cleanup steps described above.
However, in my mind, this seemed a bit extreme, as WSUS already knows what files need to be pushed out to workstations when they ask for an update. So, theoretically, it should be possible to query WSUS about what files are needed to support the updates it knows about, compare that to the files stored on the file system, and delete any files which aren’t referenced by any updates.
I sleuthed around for a few days, off and on, and could not find any articles that described a process like this. I did figure out how to determine what a particular update file is called and where they are located on the file system, but I wasn’t finding anything to link an update to the files. The WSUS database refers to files in many tables through a binary field named FileDigest. The textual representation of its hexadecimal value matches the name of the actual file in the file system, which I verified by locating several update files on the file system and comparing their details to what was recorded in the WSUS database.
Then I found this article in which a comment by ClaudioG64 described how to query for the files needed for a particular update. This way, you could manually grab them from the WSUS server, copy them to another server that couldn’t talk to WSUS, and apply them. The critical piece was the WSUS stored procedure he referenced, which is named spGetInstallableItems.
I looked at the output from it after calling it for one of the updates on my WSUS server. I then looked at the code in the stored procedure and unwound the queries to work backwards to a point where I could get a FileDigest value. After that, I just needed a list of the files on the file system to compare to, and an outer join would tell me what files matched to the updates in WSUS and what files had no corresponding update in WSUS. Generating the list was a cinch using Powershell, where I could query for particular values and export it to a .CSV file for later import.
In Powershell 2 or earlier, as is found on Windows Server 2008 R2 and earlier (unless you’ve upgraded it), use the following snippet:
Get-ChildItem .\* -Recurse | Select-Object FullName, BaseName, DirectoryName, Length | Export-Csv C:\temp\WsusContent.csv -NoTypeInformation
In Powershell 3 or later, as is found on Windows Server 2012 and later, use the following snippet:
Get-ChildItem .\ -Recurse -File -Include * | Select-Object FullName, BaseName, DirectoryName, Length | Export-Csv C:\temp\WsusContent.csv -NoTypeInformation
The different snippets above are necessary to deal with some functional and syntactical differences arising as Powershell evolved over the years. The first snippet only returns files, leaving out folders, but only works properly in Powershell 2 or earlier. In Powershell 3 and later, the syntax changed, and the first snippet, while it works, returns both files and folders, which causes problems in what needs to happen later on in this process. The second snippet uses appropriate syntax to return only files, but its syntax does not work in Powershell 2.
Open an elevated Powershell command prompt, switching into the folder that contains the WSUS files, and then run the above command (which is entered as a single line). It dumps the list of files out to a CSV file, creating C:\temp\WsusContent.csv, though you can specify a different file location that works for you. The file name I chose, which will be the name the import tool used later defaults to, doesn’t conflict with any table names in the WSUS database, and reflects the name of the folder the files are kept in. The WSUS files are usually located off the root of a drive (C: or D: in most cases) in the following folder:
When the above Powershell command was run for my problematic WSUS server, it produced a file with just over 476,000 rows in it. Each row provides:
- the full path and name of the update file
- the name of the update file with no path information or file extension
- the path of the directory the update is located in
- the size of the file in bytes
This structure allowed me enough information to work with it to verify a few things while building and testing my queries. But first, the data needs to be brought into the WSUS database. Unfortunately, I was unable to create tables in the Windows Internal Database on the WSUS server itself, so I wound up pulling a copy of the WSUS database files down to my workstation and attaching them to my local SQL Server 2016 Developer Edition instance. If you’re using a full SQL Server installation for WSUS, you may have rights to create tables. I created a table named WsusContent to import the data into, using the following SQL script:
USE [SUSDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[WsusContent] ( [FullName] [varchar](250) NOT NULL ,[BaseName] [varchar](250) NOT NULL ,[DirectoryName] [varchar](250) NOT NULL ,[Length] [int] NOT NULL ,[FileDigest] [binary](20) NULL ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_WsusContent_FileDigest] ON [dbo].[WsusContent] ( [FileDigest] ASC ) WITH ( PAD_INDEX = OFF ,STATISTICS_NORECOMPUTE = OFF ,SORT_IN_TEMPDB = OFF ,DROP_EXISTING = OFF ,ONLINE = OFF ,ALLOW_ROW_LOCKS = ON ,ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] GO
It also sets up an index on the FileDigest column, which starts out empty but will be filled in after the main data import. This index will be useful later on in the process. From there, it was a simple matter to tell SSMS to import the contents of the WsusContent.csv file into the new table. In the import wizard, you need to tell it the following things:
- the source data is coming from a flat file, which is WsusContent.csv
- the column names are in the first data row of the source data file
- set the text qualifier to a double quote
- adjust the size of the first three columns (FullName, BaseName, and DirectoryName) to be 250 characters
- adjust the size of the last column (Length) to be a 4-byte signed integer
- the data is being imported through a SQL Server native client connection
- it should automatically pick the WsusContent table, at least if the source data file has the same name as the table created by the script above
The import itself should complete fairly quickly, and you should be able to query the new table through SSMS to confirm it has data. Once the data is imported, run the following SQL script to populate the FileDigest column:
UPDATE dbo.WsusContent SET FileDigest = convert(BINARY (20), '0x' + BaseName, 1)
This command converts the base file names (the file’s name without the extension) into binary values. This binary value can then be matched to FileDigest values recorded elsewhere in the WSUS database, which is done in a query a little further down in this article.
Note that it is possible you can encounter an error with this UPDATE, especially if working with newer versions of WSUS. One reader, James, reported encountering a file named “anonymousCheckFile.txt” in the WSUSContent folder, which doesn’t exist in the folders for WSUS 2008 R2. The error is caused by the above query not being able to convert “anonymousCheckFile” to a binary value, as it isn’t a hexadecimal value. Simply delete this row out of the dbo.WsusContent table (or the CSV file before doing the import), and the command should work.
After some tinkering and testing, I came up with the following SQL query to determine what update files on the file system have no corresponding update information in the WSUS database. I carefully checked it in my testing to ensure that the number of files on the file system matched the total number of files needed for the WSUS updates plus the total number of files on the file system that had no matching WSUS update entry. I didn’t want to be removing files that were needed for updates.
The query below is structured to create commands that can be saved into a “batch” file and run from an elevated command prompt. Here’s the query:
SELECT 'del "' + y.FullName + '"' FROM WsusContent y LEFT OUTER JOIN ( SELECT DISTINCT wc.FullName ,wc.FileDigest FROM WsusContent wc JOIN tbFile f ON f.FileDigest = wc.FileDigest JOIN tbFileForRevision ffr ON ffr.FileDigest = f.FileDigest JOIN tbRevision r ON r.RevisionID = ffr.RevisionID JOIN tbProperty p ON p.RevisionID = r.RevisionID ) x ON y.FileDigest = x.FileDigest WHERE x.FileDigest IS NULL
The results of this query can be exported to a text file. For my WSUS system, the resulting file had somewhere over 440,000 lines in it. You may need to do some cleanup on it because of the way SSMS exports the data for particular file formats.
If you export the results as a tab-delimited file, the file requires no modifications, so you can skip past the steps below.
If you export the results as a comma-delimited file, the file needs the following modifications. This is because it encloses each of the lines in double quotes, and converts the double quotes around the file names to a pair of double quotes. The lines start out looking like this:
They all need to be fixed up so that they look like this instead:
Thankfully, this is quick and easy to do with the search and replace features of a good text editor like Notepad++. You can do the following search/replace for the entire file, in the following order:
- search for “”” (three double quotes) and replace with “ (a single double quote); this handles the double quotes at the end of each line
- search for “del “ (there is a space before the final double quote) and replace with del[space]; this handles the start of the line; also note that the [space] indicates a single space should be entered
Finally, save the updated text file.
Note: Because of the way this blog tries to format posts in a “smart” way, I don’t recommend copying and pasting the search/replace text from above, as the double quotes above are likely formatted as “smart” quotes instead of as a normal ASCII double quote (character 34).
The export file can now be renamed to have a BAT extension turning it into a batch command file. Copy the batch file over to the drive on the WSUS server that holds the WsusContent folder.
All that’s left to do is open an elevated command prompt on the WSUS server and run the batch file. When I did so, it took a couple of hours to run. Once it was done, the drive’s free space had increased from about 40GB to just over 1TB, leaving about 600GB of updates on it. After that, I defragmented the drive for good measure.
I hope this article helps some other WSUS admins who are struggling to clean up their servers. Of course, I appreciate any feedback on this process.