SCCM WSUS DB Cleanup Activities

The complete guide to Microsoft WSUS and Configuration Manager SUP maintenance.This post will help you to fix SCCM WSUS maintenance. This post will help the admins who manage the SCCM infrastructure. There are several other blogs about the same topic, but I’m sharing my implementation experience with this post. I have been inspired by the blogs mentioned in the references section of this post.


Steps Involved in SCCM WSUS Maintenance Tasks

There are three (3) steps involved in these SCCM WSUS Maintenance tasks. These three tasks are explained in the below sections of the post with more details.

1. Re-index the WSUS DB
2. Cleanup Obsolete Updates
3. Decline superseded updates

Important:

1. Do not sync your SUPs during this SCCM WSUS Maintenance process as it is possible you will lose some of the work you have already done if you do.
2. Deselect the non-patched Product and Classification in Software Update Point (SUP) configuration.

Step 1

• Run the following queries against SUSDB:

Use CM_SUSDB
Go
exec sp_msforeachtable 'update statistics ? with fullscan'
Go

Step 2

• As soon as Update statistics will finish, you can continue with Rebuild indexes:

Use SUSDB
Go
Exec sp_MSForEachtable 'DBCC DBREINDEX (''?'')'
Go

Step 3

• After having finished Rebuild indexes, last step is to perform the cleanup of superseded/expired updates by running below query.

**************************************************************

DECLARE @var1 INT
DECLARE @msg nvarchar(100)
CREATE TABLE #results (Col1 INT)
INSERT INTO #results(Col1) EXEC spGetObsoleteUpdatesToCleanup
DECLARE WC Cursor
FOR
SELECT Col1 FROM #results
OPEN WC
FETCH NEXT FROM WC
INTO @var1
WHILE (@@FETCH_STATUS > -1)
BEGIN SET @msg = 'Deleting ' + CONVERT(varchar(10), @var1)
RAISERROR(@msg,0,1) WITH NOWAIT EXEC spDeleteUpdate @localUpdateID=@var1
FETCH NEXT FROM WC INTO @var1 END
CLOSE WC
DEALLOCATE WC
DROP TABLE #results

***************************************************************

Please note, that depending on when you did the last WSUS clean-up, the procedure might take from 4-5 hours to 2 days. We strongly recommend that you start the clean-up process at the end of the business hours, so that we make sure that the users will not be affected

After finishing the previous steps, please execute step 1-2 again (rebuild index + update statistics):