Cleaning out old WSUS update files

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.

The Background

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:

WSUS Reset Server Node error message
WSUS displaying the “Reset Server Node” error message.

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:

  1. 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.
  2. Stop a few services on the WSUS server so that the following steps don’t prevent various maintenance steps from working.
  3. Take a backup of the WSUS database in case something goes horribly wrong during the cleanup.
  4. Run a script to rebuild/optimize the indexes in the WSUS database, which will make the other steps run faster.
  5. Run a WSUS stored procedure to determine how many obsolete updates are recorded in the database.
  6. Run a script to remove a batch of those updates from the database.
  7. 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.
  8. Shrink the database files to reclaim space, and then rebuild/optimize the indexes.
  9. 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:

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

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:

WSUS\WsusContent

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:

"del ""D:\wsus\WsusContent\01\9AC0F6B420813C870A63F8F252D0739A71D41A01.exe"""

They all need to be fixed up so that they look like this instead:

del "D:\wsus\WsusContent\01\9AC0F6B420813C870A63F8F252D0739A71D41A01.exe"

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:

  1. search for “”” (three double quotes) and replace with (a single double quote); this handles the double quotes at the end of each line
  2. 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.

Advertisements

14 thoughts on “Cleaning out old WSUS update files

Add yours

  1. Hello,
    You write great article. I get in same problems with my WSUS servers. .
    I have some problems when i try to get throw this part:

    UPDATE
    dbo.WsusContent
    SET
    FileDigest = convert(BINARY (20), ‘0x’ + left(Name, len(Name) – 4), 1)

    It looks like you missed part of command. Command only cut last four characters and populate FileDigest column. Never looked for entire database is there recorded any FileDigest value

    Sorry if my English is bad

    Thanks in advance,
    Vladimir

    Like

    1. Thanks for the feedback, Vladimir.

      I ran that SQL command yesterday while doing some routine cleanup of my own WSUS machine, and it worked without a problem. What you describe is all that the command is supposed to do. It simply populates the FileDigest column in the WsusContent table created earlier.

      A query that appears a little later in the article ties that WsusContent.FileDigest column to another table in the WSUS database. That query returns a set of “del” commands which can be saved out to a text file to be run from a command prompt against your WSUS machine to delete files which were downloaded but for which there are no updates listed in the WSUS database. Of course, you first have to do all the cleanup steps described at the beginning of the article.

      I hope that description helps.
      Ted

      Like

    2. Please note that I’ve updated the article to be a little clearer about what that SQL command really does. Based on your feedback I read the original bit of text following it and saw how it could be confusing. I believe in clarity in my writing, so thanks for pointing out an item that wasn’t especially clear. 🙂

      Like

      1. Thanks for fast response, it works now.
        This is most complete article about maintain WSUS i have read. And the only one that explain how to maintain WSUS storage folder without “reset WSUS and start again.”
        Thank you very much for your work.

        Best regards,

        Vladimir

        Liked by 1 person

  2. Hello,
    I am having some issues near the end with this code:
    UPDATE
    dbo.WsusContent
    SET
    FileDigest = convert(BINARY (20), ‘0x’ + left(Name, len(Name) – 4), 1)

    The error I get is:
    UPDATE
    dbo.WsusContent
    SET
    FileDigest = convert(BINARY (20), ‘0x’ + left(Name, len(Name) – 4), 1)

    Now my guess tells me that there is some blank values and we’re subtracting from blank values. Do you have any code to be able to skip blank values in this case? I’m not a SQL DBA or developer – just someone lightly familiar with wsus. Thanks!

    Like

    1. The issue you’re having is likely related to a Powershell command difference between different OS versions. Have a look at the answer I wrote to James Stephens. I’ve also updated the article to incorporate the command I provided to James.

      Like

      1. The command Ted provided *may* be your solution.
        The other issue I had with converting this was a file called anonymousCheckFile.txt in the WSUSContent folder. I had issues converting that to binary(20), so I added a line immediately before doing the Filedigest binary(20) conversion. That line deleted anonymousCheckFile.txt from the dbo.WSUSContent table, allowing the conversion to take place.

        There are also a couple of other things I’ve changed from Ted’s original script – for example I have used BaseName instead of Name so I didn’t have to strip file extensions (if you do this, make sure your Create Table creates BaseName instead of Name as well or the import-csv will fail).

        I’m sure there’s numerous other things that are different in Server 2016 (and SQL 2017), but these things (and their solutions) have the entire catalogue of WSUS cleanup scripts working well for me right now.

        Thank you Ted.

        Like

      2. That “anonymousCheckFile.txt” file must be an artifact of a newer version of WSUS, as it doesn’t exist in the 2008 R2 version. Thanks for catching that, and for the suggestion of using the BaseName field instead of Name, as that can definitely make the conversion simpler. I’ll give it a try the next time I perform a cleanup on my installation.

        Like

  3. Hello Ted,

    thanks for this great article. I had to change dbo.WsusContent.Length to bigint, because the updates keep getting bigger and four byte integers are no longer enough.
    Unfortunately the last SQL query that should generate the batch file to really delete the old files doesn’t return any results with my WSUS (2012 R2). Are there any differences between the versions of WSUS in this regard?

    Best regards,
    Ulrich

    Like

    1. Thanks for the note about the dbo.WsusContent.Length column. I haven’t run into that issue, likely because our WSUS installation isn’t set to pull down any of the bigger updates.

      I unfortunately haven’t had an opportunity to try out this method on the version of WSUS for Windows Server 2012 R2, and so I’d suspect that there’s some difference, especially if running that query isn’t giving you an outright error. Note that this query will only return results provided you’ve gone through the prior steps to delete the update data out of the WSUS database, which can take a while. It relies on not being able to match an update to a file on the file system (from the list of files you generated and imported into dbo.WsusContent).

      With all that said, we are going through some migrations and upgrades of some servers at the office, and we plan to push to a newer WSUS version. I’ll put this on my “to do” list and see if I can figure out what’s different between the two versions. If I can figure it out, I’ll post an update here. Of course, if you care to pick through the SQL query and fix what’s wrong, please let me know what you come up with and I’ll share it.

      Like

  4. Hi Ted,

    I’m having the same issue as Dustin here. After checking the CSV file, it appears a bunch of the directories are listed in it with two character long Name columns. Using your script I’m assuming would remove the extension length of 4 characters, which simply aren’t there (2-4=-2). As a result, I’m having the same issue as they are (although perhaps not with the same cause as they haven’t followed up here).

    Do you have a solution? I’m trying to automate as much of this as possible through Powershell and using Invoke-Sqlcmd so if you have any thoughts on streamlining it into that, it would also be much appreciated.

    Regards,
    James

    Like

    1. I did a little digging around on my end here, figuring it might be a difference in how PowerShell works on later versions of Windows Server. I built the command to generate the CSV file on a Windows Server 2008 R2 machine. What I found was that on Windows Server 2012 R2, the PowerShell command was giving you both folders and files in the list, and the rest of what followed it in the instructions assumed it was only getting files. Because of that, you’re seeing those two-character Name fields, which are actually folders. You’d also see that the Length field is empty.

      Below is an updated version of the command that provides only files:
      Get-ChildItem .\ -Recurse -File -Include * | Select-Object FullName, Name, DirectoryName, Length | Export-Csv C:\temp\WsusContent.csv -NoTypeInformation

      I’ll update the article with this command.

      Like

      1. You’re quite right.

        I’m on Server 2016 so that’s probably the reason – the Length field was correct though.

        It can be quite annoying how there are these subtleties between versions with little to no warning if you don’t use them regularly. I’ll try this command on Monday if I get a chance and see how it (and the rest of the scripts) goes.

        Ted Statham commented: “I did a little digging around on my end here, figuring it might be a difference in how PowerShell works on later versions of Windows Server. I built the command to generate the CSV file on a Windows Server 2008 R2 machine. What I found was that on Windows”

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.

Up ↑

%d bloggers like this: