How to Delete Driver Updates from WSUS

I ran into the issue where driver updates downloaded into a WSUS infrastructure that had master/replica servers created a bit of a performance problem. Several weeks into in, my WSUS replica could no longer synchronize successfully with the master. No matter what I tried it just would not synchronize anymore. I wrote about the issue here and referred to Microsoft blog post talking about the issue here.

I wasn’t able to find a way to “drop” driver updates from my WSUS WIDS database on the Internet and was left with the only option – reinstall both WSUS servers, and then reconfigure and resynchronize everything. That looked unexciting.

I thought I could as well try to delete driver updates directly from the database. What can possibly go wrong… and even if it does, I have to reinstall anyway.

So here it goes. USE AT YOUR OWN RISK.

Preparations

If you ran WSUS defrag scripts, you already have SQL Native Client and SQL Command Line tools installed on your WSUS servers. If this is the case,

  1. Uninstall SQL Native Client
  2. Install SQL Server Management Studio (SSMS) for SQL Server 2008 R2 Express SP2

If you do not have SQL Native Client installed on the WSUS servers, just go ahead and install SSMS. SSMS will fail to install if you already have Native Client.

The following steps need to be executed once on each WSUS server in your environment.

Connecting to WSUS WIDS Database

Open SSMS and connect to the following SQL instance:

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

If you get a security/access rights error, close SSMS, restart it using elevated context (right-click, run as administrator). This will get your SQL Server Management Studio connected to the WIDS database. If your database is not hosted in WIDS, chances are you are not experiencing the timeout errors anyway and don’t need to do this, but if you are doing it anyway then connect to your named or default SQL instance as appropriate.

Determine GUID of the Driver Update Type

Open a new query window and run the following two queries:

USE SUSDB
GO
SELECT UpdateTypeID FROM tbUpdateType WHERE Name = 'Driver'
GO

This query gives you the GUID that you will need to substitute in all subsequent queries (if the GUID you get is not the same as what I have in subsequent statements). In my case, it is D2CB599A-FA9F-4AE9-B346-94AD54EE0629. I saw this GUID in several WSUS databases so I think it does not change – at least not between WSUS 3.0 SP2 servers.

Delete Drivers from Tables with Foreign Key Constraints

The bad news is that WSUS database has over 100 tables. The good news is that SQL allows to enforce referential integrity in data model designs, which in this case can be used to essentially reverse engineer a procedure, that as far as I know isn’t documented anywhere.

Trick is to delete all driver type records from tbUpdate table – but FIRST we have to delete all records in all other tables (revisions, languages, dependencies, files, reports…), which refer to driver rows in tbUpdate. Here’s how this is done, in 16 tables/queries. I recommend running each query separately.

delete from tbrevisionlanguage where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629')) 
delete from tbProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbLocalizedPropertyForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbFileForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbInstalledUpdateSufficientForPrerequisite where prerequisiteid in (select Prerequisiteid from tbPreRequisite where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629')))
delete from tbPreRequisite where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbDeployment where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbXml where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbPreComputedLocalizedProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbDriver where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbFlattenedRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbMoreInfoURLForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'))
delete from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629')
delete from tbUpdateSummaryForAllComputers where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629')

The last query is really what we came here for:

delete from tbUpdate where UpdateTypeID = 'D2CB599A-FA9F-4AE9-B346-94AD54EE0629'

If at this point you get an error saying something about foreign key constraint, that will be most likely due to the difference between which reports I ran in my WSUS installation and which reports were ran against your particular installation. Fortunately, the error gives you exact location (table) where this constraint is violated, so you can adjust one of the queries in the batch above to delete references in any other tables.

These 16 queries, in my case, dropped a total of 1,381,184 rows from each SUSDB database (each WSUS server has its own, obviously). Don’t forget to do this on all WSUS servers.

Verify Functionality and Try to Synchronize WSUS Hierarchy

This should be pretty straight forward and self-explanatory. You will want to make sure that your WSUS systems are functioning as expected before declaring victory.

  1. Run index defrags on all WSUS databases (see articles cited at the top)
  2. Restart WSUS console and look for Driver updates (you should not see any)
  3. Click around in WSUS console and look for any weirdness
  4. Run WSUS Cleanup Wizard from bottom to top of your WSUS infrastructure
  5. Synchronize Master WSUS server with Microsoft
  6. Now try to synchronize Replica WSUS servers with the Master

If all is well so far, it’s looking good but there’s more.

  1. Look for any Needed and Unapproved patches, and Approve them
  2. Run wuauclt /detectnow on one or two of the servers
  3. Servers should pick up updates; install them and reboot if necessary
  4. Run wuauclt /reportnow and ensure that reports are submitted/accepted by replica WSUS
  5. Synchronize replica WSUS server with the master once more and confirm that reports are rolling up from replica to master

 

A PowerShell Afterthought

Similar functionality can also be done through WSUS API, implemented in Microsoft.UpdateServices.Administration assembly (in C:\Program Files\Update Services\Api, by default). If you use PowerShell, deleting declined updates using this assembly might look something like this:

[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")

$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer();

$wsus.GetUpdates() | Where {$_.IsDeclined -eq $true} | ForEach-Object {$wsus.DeleteUpdate($_.Id.UpdateId.ToString()); Write-Host $_.Title removed }

This runs quite well, and does the job described earlier in the SQL scripts, perhaps in a more elegant and quite possibly in a more supported way; that said, deleting updates through the API is a very slow process – the rate I was seeing was around 180 updates an hour. So if your system stopped synchronizing because its declined update count crossed into 1000+ territory, don’t expect this powershell shortcut to be a quick fix. SQL scripts do the job many times quicker.

Probably the best way to deal with this issue is to setup this PowerShell script to run nightly, along with database index defrags. See Downloads page for the scripts I use.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>