Meghan Stewart states that "I have also seen extremely conscientious Configuration Manager administrators be completely unaware that WSUS maintenance should be run at all. After all, most of us just setup WSUS servers since it is a prerequisite for a Software Update Point (SUP). Once the SUP is setup, we close the WSUS console and pretend it doesn’t exist anymore.
Well, I am one of those sccm administrators and I never heard such a thing before. Anyways. Here is the instruction how to cleanup and implement maintenance on WSUS written by Meghan Stewart but it didnt work for me. I had different experience. When I tried to run WSUS Server Cleanup Wizard, it timed out every time and Meghan's suggestion to check only the first checkbox when running the wizard didnt solve the problem.
Meghan Stewart says it is expected and we need to do the following if clean up times out. He says:
"Please be aware that if the WSUS Server Cleanup Wizard has never been run and the WSUS has been in production for a while, the cleanup may time out. In that case, re-index with Steps 2 and 3 first, then run the cleanup with only the top box checked (unused updates and updates revisions). This may require a few passes. If it times out, run it again until it completes, then run each of the other options one at a time. Lastly make a “full pass” with all options checked. "
I tried it countless times and CleanUp timed out eveyrtime I try. So, I kept reading tech-net Forums and found out there are many wsus admins who have the same problem.
Some guys solved the problem by cleaning up first a few obsolete updates from the database. use the query below to list obsolete updates
USE SUSDB
GO
exec spGetObsoleteUpdatesToCleanup
Then remove a few of these listed obsolete updates with the Query below. (XXXXXX is LocalUpdateID). Guys in the forum say that deleting the updates is slow at the beginning, it takes a few minutes to delete one update. When it becomes fast, that means you can now use the cleanup wizard and the timeout problem is solved. Some guys solved the problem by deleting 2 obsolete updates from the top of the list and some delete 10 of the obsolete updates. When I tried the same thing, this didn't solve my problem no matter how many times I manually delete those updates :( The deleting process has never been faster. At this point I had to find another way because I can not just delete 17000 obsolete updates one by one.
exec spDeleteUpdate @localUpdateID=XXXXXX
SOLUTION:
On the same Forums page, One guy named Russ shared a script which deletes all obsolete updates one by one. I know this would take a long time but somewhere in the process the deleting will become really really fast. Here is an index you can add to the WSUS table that dramatically improves the performance.
First run this query on SQL Studio Management
USE [SUSDB]
GO
CREATE NONCLUSTERED INDEX [IX_tbRevisionSupersedesUpdate] ON [dbo].[tbRevisionSupersedesUpdate]([SupersededUpdateID])
GO
CREATE NONCLUSTERED INDEX [IX_tbLocalizedPropertyForRevision] ON [dbo].[tbLocalizedPropertyForRevision]([LocalizedPropertyID])
GO
Stop WSUS Service on SCCM server.
ReIndex WSUS Database
Goto following web page and copy-paste the script in a new query on SCCM SQL Database server and hit Execute on SQL Management Studio.
https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61
Then Run the script below, Execute as a Query on SQL WSUS database. You can see the deleted obsolete updates under the messages section of the query. This will delete 101 records each time you run the script. You can change that number IF @curitem < 101. Start with 10 and see how long it takes, then do 100 and more. During my test, this number couldn't exceed 2499. If the script fails or is stopped it will have to start all over because it doesn’t commit the changes until it completes successfully.
At some point, you will notice that deleting process will be so fast and it will be done in seconds not in minutes. That means you can use the WSUS CleanUp wizard without getting timeouts. Run the script until deleting process becomes fast. Then use WSUS Cleanup wizard as Meghan Stewart recommends in his article.
USE SUSDB
DECLARE @var1 INT, @curitem INT, @totaltodelete INT
DECLARE @msg nvarchar(200)
CREATE TABLE #results (Col1 INT) INSERT INTO #results(Col1)
EXEC spGetObsoleteUpdatesToCleanup
SET @totaltodelete = (SELECT COUNT(*) FROM #results)
SELECT @curitem=1
DECLARE WC Cursor FOR SELECT Col1 FROM #results
OPEN WC
FETCH NEXT FROM WC INTO @var1 WHILE (@@FETCH_STATUS > -1)
BEGIN SET @msg = cast(@curitem as varchar(5)) + '/' + cast(@totaltodelete as varchar(5)) + ': Deleting ' + CONVERT(varchar(10), @var1) + ' ' + cast(getdate() as varchar(30))
RAISERROR(@msg,0,1) WITH NOWAIT
EXEC spDeleteUpdate @localUpdateID=@var1
SET @curitem = @curitem +1
IF @curitem < 101
FETCH NEXT FROM WC INTO @var1
END
CLOSE WC
DEALLOCATE WC
DROP TABLE #results
What If you are using WSUS Internal Database:
If you use Internal DB for WSUS, You need to first install SQL Management Studio on your WSUS server and then connect SQL Server with named pipes protocol.
First run SQL Server Configuration Manager (comes with SQL Management Studio)
Navigate SQL Native Client> Client Protocols> named Pipes> Enabled
Start SQL Server Management Studio (Run as Administrator) > In Server Name, enter "\\.\pipe\MICROSOFT##WID\tsql\query"
Click Options> Network Protocol: Named Pipes
Hit Connect
Now you can run the script that I mentioned in Solution section.
AUTOMATIC CLEANUP OF WSUS
1. Decline Superded Updates via SCCM or Using Powershell
Decline Superseded Updates, so client can scan updates more efficiently.
OR You can use this script below to decline superseded updates via Task Scheduler (This PowerShell code that will automatically decline all updates with a status of anything except for declined and has at least 2 superceding update)
PS Script:
Get-WSUSUpdate -Classification All -Status Any -Approval AnyExceptDeclined `
| Where-Object { $_.Update.GetRelatedUpdates(([Microsoft.UpdateServices.Administration.UpdateRelationship]::UpdatesThatSupersedeThisUpdate)).Count -gt 1 } `
| Deny-WsusUpdate
2.Run Clean Up for WSUS Powershell script via Task Scheduler (I will schedule this 1 hour after the re-index task. Every months' 5th day at 1:00AM.)
Invoke-WsusServerCleanup -CleanupObsoleteComputers -CleanupObsoleteUpdates -CleanupUnneededContentFiles -DeclineExpiredUpdates
You can also add -DeclineSupersededUpdates switch to the PS command above and it will decline the superseded update immediately but I dont want to decline SupersededUpdates if there are not 2 superseding of a specific update.
1. Automate Re-Indexing WSUS database.
Copy the script from this link:
https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61
Open SQL Server Management Studio and connect to your WSUS instance. Expand Management, then right-click on Maintenance Plans and select New Maintenance Plan. Give your plan a name.
Click on subplan and make sure Toolbox is visible. Drag and Drop the task "Execute T-SQL Statement Task". Right click on it, Edit and Paste the script in it. Click OK.
Click On Schedule. I will run this every months' 5th day at 2:00AM.