After I did some maintenance on my WSUS with some tricks I still had problem, after hours of cleanup, PowerShell script stopped working and constantly gave me a timeout.
I did not had time to investigate the issue, but I got saved in my disk a small notes I’ve found somewhere that suggested how to cleanup manually directly with SQL.
In such situation my temptation to wipe out everything and start a new WSUS is really high, but I always resort on spending a little time trying to do some manual tentative. I’ve started looking at how the stored procedure is defined.
This some pretty standard SQL, nothing obvious stands out, but one thing bothers me a lot: line 13 does a distinct and does not limit output in any way. This is perfectly fine, but this forces the query to examine all the updates to find all the candidates to delete. So I duplicated the stored, and instead of a SELECT DISTINCT I used a simple SELECT top 100 to limit record to return.
Limiting the number of obsolete update to retrieve is perfectly fine for me, I can cleanup in batches.
Launching the new stored procedure I got a pleasant surprise, because it took only 10 seconds to give me the first 100 updates to delete. Clearly some of the results are duplicated, I got the same update more than one time, but nevertheless I was able to got 89 unique results and immediately proceed to cleanup those ones.
The strategy of cleaning up in batch can be tedious, but it can be automated with a simple cursor and in my situation it seems that it is the only way to go to cleanup my server.
Clearly this is probably my last manual intervention on WSUS, the next problem will determine the end of it, I’ll move to a new server, more powerful hardware hoping to have an usable installation.
As always, if you plan on using WSUS server, consider having it virtualized to move to a more powerful hardware if needed or at least for cleanup
If you wonder what is the most resources used by WSUS, at least in my experiences, it is always CPU. This is usage of the computer during the cleanup procedures, it is constantly using both the CPU and still has plenty of memory. Disks are not impacted.
Figure 2: WSUS resource usage