WSUS DB Tables - Large Tables

[shockotechcom]

I have a tiny WSUS downstream server (10 clients) but the DB is 12 GB. I'm looking at the following tables that have the most records and occupy the most space:

Can anyone explain these to me or point me in the direction of their documentation?

[#2]

Focusing on the table you've marked in red, tbXml, this table contains the metadata for every update the server has been configured to synchronise.

Reasons this can blow out in size is that someone at some point enabled "too many" update categories to synchronise, for example all categories, all products or even just the drivers category - which has always been large.

I'm not clear on what the server clean-up task does these days, but many years ago when I last made the mistake of looking at the drivers category, the metadata remained, making the only way to shrink the database to re-sync it from the upstream parent.

Your first port of call would be to compare the number of rows on this downstream replica to the parent. If the parent is considerably less then maybe nothing's changed in which case the "best" course of action is to re-stage the downstream replica (assuming your WAN can handle it, etc.)

Also check the language settings and ensure you don't have more languages enabled than necessary.

[#3]

Yes that looks like it to me! In the past on these servers someone did indeed enable the drivers category. They started to fall over so I ran some code against the API to delete the driver updates (17000+!) because I couldn't decline them as these were downstreams servers. They had already been declined at the upstream/primary. What I should have done is make the downstream a primary and then declined them as I think when you decline it runs some stored proceedures to cleanup up the events and meta data tables.

[#4]

I also checked and in fact the upstream servers was also changed so these downstream servers were moved from one primary to another. I wonder does the downstream, inherit everything from the parent and remove things it doesn't (seems not?!) ?

[#5]

@shocko 

Honestly, in your position, I'd just remove the feature and set it up again. Removing the feature does not remove the downloaded content, which helps minimise the impact if you're dealing with a branch office with mediocre connectivity.

The loose logical sequence of events would be:

1. Remove the troublesome downstream replica from the upstream parent, then on the downstream replica;
2. Remove WSUS features;
3. Reboot (call me paranoid);
4. Optional: Remove the remote SQL database (I never use WID if I can help it);
5. Install the WSUS features;
6. Use "wsusutil.exe postinstall" to ensure it's configured to re-use the existing content directory (see article linked below);
7. Optional: In the same command, point it to a remote SQL host (again, just my preferred method);
8. Once "postinstall" has finished, add it back as a downstream replica to the upstream parent;
9. Once it's finished it's initial synchronisation - which is going to take a while, no doubt - run the usual "server clean-up" process using either the MMC, WSUS PowerShell module or wsusutil.exe;
10. Prosper.

When I say "take a while" in step 9, I don't mean lots of downloading - though that may also happen if the approvals are significantly different. It takes WSUS a long time to set up the database (which includes pulling the relevant update metadata from the replica's parent), which is what I'm referring to.

If you have multiple content directories and are not sure which one is being used, check the registry under HKLM\SOFTWARE\Microsoft\Update Services\Server\Setup\ContentDir.

Article explaining "wsusutil postinstall" (and other stuff) here. The article's from Server 2012 R2, but I've seen no differences using Server 2019. (If it ain't broke, don't fix it)