sccm 2012

SCCM WSUS Maintenance – Cleanup Wizard time out

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



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


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



CREATE NONCLUSTERED INDEX [IX_tbRevisionSupersedesUpdate] ON [dbo].[tbRevisionSupersedesUpdate]([SupersededUpdateID])


CREATE NONCLUSTERED INDEX [IX_tbLocalizedPropertyForRevision] ON [dbo].[tbLocalizedPropertyForRevision]([LocalizedPropertyID])


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.

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.


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




BEGIN SET @msg = cast(@curitem as varchar(5)) + ‘/’ + cast(@totaltodelete as varchar(5)) + ‘: Deleting ‘ + CONVERT(varchar(10), @var1) + ‘ ‘ + cast(getdate() as varchar(30))


EXEC spDeleteUpdate @[email protected]

SET @curitem = @curitem +1

IF @curitem < 101





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.


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:

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.

Post Comment