WSUS Replica Server Fails to Synchronize

WSUS infrastructure consisting of master WSUS server and one or more downstream replica servers may, at some point, stop synchronizing successfully. If you enable driver updates in your WSUS infrastructure, this sync issue may happen as quickly as 2 weeks after the WSUS server build.

Most frustratingly, running index defrag on WSUS database or/and running WSUS Cleanup Wizard do nothing to fix the issue.

Symptoms

When you attempt to synchronize downstream server with the master, sync progress goes to 9x% (98% or close to that) and then sits without any subsequent progress for anywhere between 2-10 minutes. It eventually errors out with a SQL exception error, typically this is a timeout. You can see this message by clicking on the Details link in the sync run details, in WSUS console.

SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
    at Microsoft.UpdateServices.DatabaseAccess.DBConnection.ReadOneRow()
    at Microsoft.UpdateServices.Internal.DataAccess.HideUpdatesForReplicaSync(String xmlUpdateIds)
    at Microsoft.UpdateServices.ServerSync.CatalogSyncAgentCore.ProcessHiddenUpdates(Guid[] hiddenUpdates)
    at Microsoft.UpdateServices.ServerSync.CatalogSyncAgentCore.ReplicaSync()
    at Microsoft.UpdateServices.ServerSync.CatalogSyncAgentCore.ExecuteSyncProtocol(Boolean allowRedirect)

Different types of timeouts point to different issues. Check out this article first to see if driver update problem applies to you.

Cause

If this is specifically a TdsParser timeout, the issue is that your WSUS system has too many hidden updates in the database, and from the looks of it, WSUS server is trying to do some XML parsing of hidden updates and is not able to process all updates for replica sync.

Hidden updates = declined updates. Number of declined updates mushrooms in WSUS databases if, ironically, you run WSUS Cleanup Wizard, which marks superseded and expired updates as declined. Declining unneeded updates is a good thing but it has this interesting side effect of breaking WSUS replication.

To confirm that this is indeed the issue, on your replica WSUS server, open SQL Server Management Studio (this article has information on how to install SSMS on WIDS WSUS database server), open Activity Monitor, expand expensive queries pane, and trigger manual synchronization run. You should see two RUNNABLE processes, one of which will be running under NETWORK SERVICE identity and showing as coming from wsusservice. Right-click on this process and click Details. You will see this command:

SUSDB.dbo.spGetUpdatesForBulkHideInReplicaSync;1

Next, run this statement above in SSMS new query window. You should get a response back from SQL very quickly (a couple of seconds) but it will contain several tens of thousands of results – possibly 50,000 or more.

If you don’t see any other queries in Activity Monitor, and your CPU utilization goes up and flattens out, taking up about one core (so ~25% CPU utilization on a 4-core system), and stays at that level for a few minutes, the article applies to your situation. The issue is that SQL server returns requested results quickly, but then WSUS server tries to do something with associated XML definitions (explaining why there are no subsequent queries and seemingly high CPU utilization) and times out.

Running this query below will also return a large number of matches, possibly as high as 50,000 or more.

SELECT * FROM tbUpdate WHERE isHidden = 1

Temporary Fix

Before putting in a more permanent (and irreversible) fix, try running this query on the replica WSUS server that can’t synchronize successfully:

UPDATE tbUpdate SET isHidden = 0 WHERE isHidden = 1

After running this query, perform a manual sync on the replica WSUS server. Most likely, your replication will succeed this time, proving further that the issue is the number of hidden/declined updates in the system.

Unfortunately, you would only be able to synchronize once using this method. The first synchronization run will synchronize isHidden flag in the tbUpdate table from the master WSUS server, and the following replication runs will fail with the same TdsParser issue.

Permanent Fix

Permanent solution involves reducing the number of hidden updates in the database. You need to manually delete definitions of hidden updates. Use the following queries to do this, and don’t forget to do it on all WSUS servers (assuming each WSUS server has a WIDS or SqlExpress database) in your infrastructure.

delete from tbrevisionlanguage where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbLocalizedPropertyForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1 ))
delete from tbFileForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1 ))
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 ishidden=1 )))
delete from tbPreRequisite where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1 ))
delete from tbDeployment where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1 ))
delete from tbXml where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1 ))
delete from tbPreComputedLocalizedProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1 ))
delete from tbDriver where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbFlattenedRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbMoreInfoURLForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbBundleAtLeastOne where bundledid in (select bundledid from tbBundleAll where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1)))
delete from tbBundleAll where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbSecurityBulletinForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbKBArticleForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbRevisionSupersedesUpdate where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbBundleAtLeastOne where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbEulaProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1))
delete from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1)
delete from tbUpdateSummaryForAllComputers where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1)
delete from tbInstalledUpdateSufficientForPrerequisite where LocalUpdateId in (select LocalUpdateId from tbUpdate where ishidden=1)
delete from tbUpdate where ishidden = 1

The key is to delete all dependent records in tables other than tbUpdate, and finally delete all isHidden = 1 records from tbUpdate table. The last statement in this script is what actually fixes WSUS replication problem.

If for some reason you are unable to run the last statement successfully, this solution will not be effective – if this happens, investigate the error message; it will tell you which other tables contain dependent records – delete them as well. At the end, you need the last statement to run without errors.

As a reminder, this script needs to be executed against all WSUS databases in the environment, using any of the methods that work (SQLCMD can be used in place of SSMS).

After running this script, navigate to the WSUS console Updates section, and look up how many Declined updates you have. The number of updates will be reduced dramatically and you may find that WSUS console works quite a bit faster as a result.

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>