WSUS Maintenance on Windows Server 2016 (and more)

Windows 10 Abstract

Painful, manual WSUS maintenance on Windows Server 2016 Standard

Windows Server Update Services (WSUS) is a phenomenal utility that comes baked into the Windows Server operating system.  However, as I have found out today, the built-in tools for keeping WSUS neat and tidy are lackluster at best.

There are literally hundreds of items you’ll find on the internet for how to maintain cleanliness of WSUS, and a lot of depends on your environment.  Are you running a single WSUS server or do you have several downstream servers that are all replicas of one?  Which version are you running? Did you use Windows Internal Database (WID) or are you using SQL Server for you WSUS database?  Do you store Windows Updates on the WSUS server or do you direct clients to fetch them from Windows Update?  These are all things that you need to be aware of in your environment prior to beginning your search.

My case is fairly straightforward.  I have a WSUS server, and several downstream servers.  I use client side targeting for sites that have their own WSUS server on-site, and those who don’t.  All of my computers reach out to Windows Update to retrieve their updates based on what WSUS tells them to go get.  I absolutely am auto-approving Critical and Security updates at a minimum.  Finally, I’m using WID for simplicity.

Today was WSUS maintenance day.  I’m not accustomed to managing WSUS, only the basics really.  But I will tell you, at least with my environment, I have become much more familiar with these tasks.  And oh how have I learned that the built-in tools for cleaning WSUS are sub-par.

There used to be a free script available (that’s probably floating around somewhere on a Sysadmin’s USB drive or something), that was magical for cleaning up WSUS (from what I’ve read).  The script is no longer free and the author has monetized it for sale.  You can read more about that here and read for further details on the author and how to purchase.

Disclaimer: I take no responsibility if you f-up your environment.  While WSUS is fairly simple to deploy, its still added work that you probably don’t want to do.  Be sure you know your environment, and use the information here as a guide.

Maintenance on the WSUS WID

This is probably something you want to do after you perform a WSUS cleanup using the built-in WSUS cleanup wizard.  Which, I haven’t been doing for years as I didn’t really know any better.  Just trusted Window’s built-in tools (silly me).

You’re going to need SQL Server Management Studio (SSMS).  You can just use SSMS 2017 and you can download that here from Microsoft.  May want to check that you have the correct permissions as well.  You’ll need to run SSMS as an Administrator.  I also used my domain admin account which is a member of the local administrators on the server to authenticate with the WID.

Database type is Database Engine and the server name for Server 2012 and 2016 is:

\\.\pipe\MICROSOFT##WID\tsql\query

You can read more on this Technet article and on the Microsoft blog.

WSUS WID SQL Connection

Now you’re going to run a SQL query against the SUSDB. The SQL script you can find here in the Technet gallery.  But also below for your convenience:

/****************************************************************************** 
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)  
EXEC sp_updatestats 
PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)  
GO 

Paste that script into a new query with your SUSDB database selected and F5 that bitc.. I mean, execute the query.  The script will take some time to run but this is good practice evidently after you run the built-in WSUS cleanup wizard.

Paste that script into a new query with your SUSDB database selected and F5 that bitc.. I mean, execute the query.Click To Tweet

Now lets cleanup some of those old updates. Powershell style.

WSUS’s built-in wizard says it will decline updates that haven’t been approved and / or have expired from Windows Update.  And it probably does. Maybe.  My environment had almost 10,000 updates that were Installed / Not applicable.  That can’t be healthy.  This was after running the cleanup wizard in WSUS.

This next task is something that you can probably automate to have done bi-weekly or monthly or whatever fits your needs via task manager. This is a very handy script that I did not modify at all, ran in my environment and it cleaned up my WSUS server drastically.  You can tailor this script to fit your needs.  Huge thank you to Nick Eales @ Microsoft for supplying this script.  You can view and download the script from the Technet gallery here.

Here is the description of the script and the syntax to use when running it:

.Synopsis 
   Sample script to decline superseded updates from WSUS, and run WSUS cleanup if any changes are made  

.DESCRIPTION 
   Declines updates from WSUS if update meets any of the following:
        - is superseded
        - is expired (as defined by Microsoft)
        - is for x86 or itanium operating systems
        - is for Windows XP
        - is a language pack
        - is for old versions of Internet Explorer (versions 7,8,9)
        - contains some country names for country specific updates not filtered by WSUS language filters.
        - is a beta update
        - is for an embedded operating system

    If an update is released for multiple operating systems, and one or more of the above criteria are met, the versions of the update that do not meet the above will not be declined by this script

.EXAMPLE 
   .\Decline-Updates -WSUSServer WSUSServer.Company.com -WSUSPort 8530

When I ran this script, over 7500 updates where cleaned up out of my environment.  F-ing beautiful.

Other cleanup tasks you can do for WSUS

I also usually go through and make sure that I have computers landing in the correct group in WSUS, there’s always a few stragglers that end up somewhere funky.    I also delete computers from WSUS that haven’t checked in for over 30 days.  They’re likely in a storage room serving as a home for some fuzzy little creatures at this point or they’ve been re-imaged with a different or literally anything else.  Good thing is, when you delete a computer out of WSUS, and maybe it was a mistake, it will come back when it checks back inSo don’t worry.

If you have client domain computers and/or servers that are erroring on installing updates

There can be many reasons for this error to happen, and as with just about anything you’ll want to review the logs or research the error message directly on the Update screen to find your solution.

For me (which I probably went a little overkill than was really required), but I cleaned the Software Distribution folder for Windows updates and I was able to continue installing the updates afterwards.

Cleaning the Software Distribution folder, removing it’s contents or renaming the folder.

You have a couple of options to force a fresh start on Windows Updates.  No, you will not reinstall every single old update.  Windows will just rebuild its data on what’s installed and what isn’t, then continue on.

Delete the contents of the Software Distribution folder

First, stop the Windows Update service.  Because the files you’ll be deleting will be in use and you won’t be able to delete them.  Open a command prompt and type:

net stop wuauserv

Or you can open the Windows Services GUI (services.msc) and find Windows Update, then stop the service from there.

Open Windows Explorer and navigate to C:\Windows\SoftwareDistribution.  Delete all of the contents of that folder, but not the folder itself.

No need to run a net start wuauserv as now you’re going to go click on Check for updates in Windows Update, which will start the service for you.

Now Windows Update will rebuild the directory and hopefully your updates will install this time.

Rename the SoftwareDistribution folder

If you’re paranoid (nothing wrong with that :P) about deleting the contents of this folder, you can rename it instead to keep the directory intact. You’ll still need to stop the Windows Update service with a net stop wuauserv command.  Then all you need to do is rename the C:\Windows\SoftwareDistribution to C:\Windows\SoftwareDistribution.old (or something).

Now run Windows Update again.  This should have a similar effect as deleting the contents of that directory.

Final things to note

If you now have a boat load of computers that need to check in after cleaning your WSUS environment, there is a way that you can force clients to check in and report their update status to WSUS.

The command is slightly different between Windows 10 and Windows 7.  Per this article from the Microsoft Docs on updates with Server 2016:

The wuauclt.exe /detectnow command has been removed and is no longer supported

So for Windows 10 / Server 2016, you now have these commands available to you:

usoclient.exe StartScan //Used To Start Scan
usoclient.exe StartDownload //Used to Start Download of Patches
usoclient.exe StartInstall //Used to Install Downloaded Patches
usoclient.exe RefreshSettings //Refresh Settings if any changes were made
usoclient.exe StartInteractiveScan //May ask for user input and/or open dialogues to show progress or report errors
usoclient.exe RestartDevice //Restart device to finish installation of updates
usoclient.exe ScanInstallWait //Combined Scan Download Install
usoclient.exe ResumeUpdate //Resume Update Installation On Boot

Huge thank you to Pranav V Jituri for sharing this information.

Feel free to continue using wuauclt.exe for your Windows 7 clients. Here are a few commands you’ll likely be interested in:

wuauclt.exe /detectnow
wuauclt.exe /updatenow
wuauclt.exe /reportnow

So if you have a means of running these commands at scale to remote machines, that will be a huge help for you if you’re impatient.  Tools like PDQ and LogMeIn Central can do these sorts of things for you but there are other solutions that you’re free to research and test in your own environment.

That’s all I have for now on Windows Update and WSUS maintenance

There is so much more to this subject, but I wanted to share my tribulations over the past week or so as I was struggling to get my WSUS reports to be accurate (and trust that they were).  If you have any added tips / advice for others (or me!), share them in the comments to help your fellow Sysadmins.  Be sure to share your source if you have one :).

You May Also Like

About the Author: Travis Wade

Just a simple man living an ordinary life with an extraordinary family. I'm an information technologist and hobbyist astronomer. Anything to do with technology and science is of total interest to me.