Fix WSUS Downstream Replica Showing More Updates than the Primary

As you can see, both servers are showing 2189 available updates out of 8590 total when the filter is set to Any Except Declined with a status of Any. The 6401 that are not shown are all Declined.

To create the problem, I just ran the following script against my primary WSUS server only.

-- To check number of hidden updates that will be deleted
SELECT * FROM tbUpdate WHERE isHidden = 1

-- To delete all of the hidden updates 
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 

After the script finished running, I forced a full software updates synchronization from within Configuration Manager by putting a file called full.syn into the C:\Program Files\Microsoft Configuration Manager\inboxes\wsyncmgr.box folder.

Once the sync completed, I refreshed the WSUS console on both servers. Let's look at the primary server first.

As you can see, there are now 2189 available updates out of 2191 total when the filter is set to Any Except Declined with a status of Any. That looks good. Now let's see what the downstream replica is showing.

Whoa, what happened? Why are there 8590 available updates out of 8592 total, when before it showed 2189 available out of 8590 total? It should be the same as the primary server, right?

The reason there is a discrepancy is because updates that are deleted from the primary WSUS database do not get removed from downstream replicas during a sync. If you just run the cleanup script on the primary WSUS server, on the next sync cycle all of the previously declined updates on the downstream replicas will flip to Not Approved, instead of being deleted. This happens because during the sync cycle, the downstream replica no longer sees the update in the primary server as declined, so the downstream replica changes the update to Not Approved.

This substantially increases the amount of metadata clients have to download, causes a mismatch between databases, and creates the problem you are trying to avoid. This happened at one of my clients who was running cleanup scripts only on the primary WSUS server. They had 7,695 available updates on the primary server, and 26,981 available updates on the downstream replica.

Now you may be saying to yourself, ok, let's just run the cleanup script against the downstream replica and that will fix the problem. Unfortunately that will not work because all of the previously declined/hidden updates are now showing as available. The cleanup script has nothing to delete!

To prevent this situation from ever happening in the first place, as per Microsoft, In a WSUS hierarchy, it is strongly recommended that you run the cleanup process on the lower-most, downstream/replica WSUS server first, and then move up the hierarchy. Incorrectly running cleanup on any upstream server prior to running cleanup on every downstream server can cause a mismatch between the data that is present in upstream databases and downstream databases. The data mismatch can lead to synchronization failures between the upstream and downstream servers.

While that's good advice for the future, how do you fix the problem now? You have two choices, first you can reinstall SUP/WSUS on the downstream replica servers, or you can follow the process detailed below.

Note: The solution provided below is only applicable if the problem was caused by running the above script. If your issue was not created by the above script, do not continue.

WARNING!!!
Failure to follow these steps exactly may cause issues in your environment. As long as you do not rush the process, and follow the instructions step-by-step, you'll be ok. The following process and all of the scripts in this post are not supported by Microsoft. I take no responsibility if you mess up your environment. Run at your own risk.

Now that that's out of the way, here's what you need to do to fix the problem. You'll need SQL Server Management Studio running on all WSUS servers.

From a high level, what we are going to do is export all of the update GUID's from the primary WSUS database, then compare that to what is on the downstream replica. If the downstream replica has an update that is not in the primary server database, it will be deleted.

Disable sync on the SUP before running this fix. Having a sync run during this process may cause issues.

Run the following steps on the Primary WSUS server that downloads updates directly from Microsoft.

From within SQL Server Management Studio, right click on SUSDB and select New Query. In the query window, enter the following commands, then click Execute. Wait until the query window shows Query executed successfully before continuing.

SELECT COUNT(*) AS [TotalUpdateIDs]
FROM [dbo].[tbUpdate]

SELECT [t0].[UpdateID]
FROM [dbo].[tbUpdate] AS [t0]

Note: The TotalUpdateIDs number will be drastically higher than the number of updates showing in the WSUS console. This is normal.

Write down the number in the TotalUpdateIDs column. You'll need that later. In my case it is showing 68325

Make sure you click on a GUID in the UpdateID column. Then right click on the GUID and select Save Results As...

Select a central location accessible to all of the WSUS servers, enter updateids.csv as the file name, verify CSV is the file type, then click Save.

Open the CSV file with Notepad and verify it is full of GUID's. If it is not, try to save the results again. Do not make any changes to the file. THIS IS VERY IMPORTANT! You just need to verify all of the GUID's are in there.

The rest of the commands need to be run on each of the WSUS Downstream Replica servers which sync from the Primary server you just ran the previous commands on. Only work on one server at a time.

Create a directory on the root a C: and call it Temp. Copy the updateids.csv file from the central location you saved it to and place it in C:\Temp

From within SQL Server Management Studio, right click on SUSDB and select New Query. In the query window, enter the following commands, then click Execute. Wait until the query window shows Query executed successfully before continuing.

-- Create Temp Table to hold UpdateID's exported from the primary SUSDB
CREATE TABLE #tmpAllUpdateIDs (UpdateID uniqueidentifier)

-- Import all UpdateID's in the csv file exported from the primary SUSDB
BULK INSERT #tmpAllUpdateIDs
FROM 'C:\temp\updateids.csv'

SELECT COUNT(*) AS [TotalUpdateIDs]
FROM #tmpAllUpdateIDs

STOP!!! Verify the number in the TotalUpdateIDs column EXACTLY matches the number you wrote down earlier. If it doesn't, DO NOT CONTINUE!!! Either the export failed, the file is corrupt, the csv file is is not named properly, or the csv file is not in C:\Temp.

In my case, the numbers do match exactly, so it's ok to continue.

Within the same query window, replace the commands you just ran with the following commands, then click Execute. Wait until the query window shows Query executed successfully before continuing.

Note: After replacing the previous commands with the below commands, the query window will show a red squiggly line under #tmpAllUpdateIDs. This is normal and expected because the code that created #tmpAllUpdateIDs was replaced, and the query editor cannot resolve the name. The #tmpAllUpdateIDs table is still in memory, which is why you needed to use the same query window.

-- Delete all updates not found on the primary SUSDB
delete from tbrevisionlanguage where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs)))
delete from tbProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs)))
delete from tbLocalizedPropertyForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs) ))
delete from tbFileForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs) ))
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 UpdateID not in (select UpdateID from #tmpAllUpdateIDs) )))
delete from tbPreRequisite where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs) ))
delete from tbDeployment where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs) ))
delete from tbXml where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs) ))
delete from tbPreComputedLocalizedProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs) ))
delete from tbDriver where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs)))
delete from tbFlattenedRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs)))
delete from tbRevisionInCategory where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs)))
delete from tbMoreInfoURLForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs)))
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 UpdateID not in (select UpdateID from #tmpAllUpdateIDs))))
delete from tbBundleAll where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs)))
delete from tbSecurityBulletinForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs)))
delete from tbKBArticleForRevision where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs)))
delete from tbRevisionSupersedesUpdate where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs)))
delete from tbBundleAtLeastOne where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs)))
delete from tbEulaProperty where revisionid in (select revisionid from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs)))
delete from tbRevision where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs))
delete from tbUpdateSummaryForAllComputers where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs))
delete from tbInstalledUpdateSufficientForPrerequisite where LocalUpdateId in (select LocalUpdateId from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs))
delete from tbUpdate where UpdateID not in (select UpdateID from #tmpAllUpdateIDs)

-- Get rid of the temp table
DROP TABLE #tmpAllUpdateIDs

Note: Depending on how many updates are in the system, this query may take a long time to finish. Be patient and let it run. Wait until the query window shows Query executed successfully before continuing.

Once the query finishes running, open the WSUS console on the server, set the filter to Any Except Declined with a status of Any, then click Refresh. Verify the number of updates matches the primary WSUS server.

As you can see, there are now 2189 available updates out of 2191 total when the filter is set to Any Except Declined with a status of Any on the downstream replica. This matches exactly the number of updates on the primary WSUS server. Your downstream replica database is now back in sync with the primary WSUS server.

Repeat these steps on all of the other downstream servers.

Since a lot of changes have been made to the database, it should be reindexed. On every WSUS server, from within SQL Server Management Studio, right click on SUSDB and select New Query. In the query window, enter the reindex code found here, then click Execute. Wait until the query window shows Query executed successfully before continuing.

When you have finished, reenable sync on the SUP.

Force a full software updates synchronization from within Configuration Manager by putting a file called full.syn into the C:\Program Files\Microsoft Configuration Manager\inboxes\wsyncmgr.box folder. Open up the wsyncmgr.log and verify the sync succeeds without errors. Once finished, load the WSUS console on every server and verify the number of updates are the same on each one.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.