As an SCCM administrator it is easy to install SCCM and simply forget about WSUS being an integral part of SCCM and forgetting its even there. That is until you start getting synchronization errors or other WSUS errors. So to fix or prevent that from happening you should really go to the wsus server cleanup wizard which can be found in the wsus console under options. What do you do though if you haven’t been running it and the wsus server cleanup wizard fails.
Well to start you can rerun the wsus server cleanup wizard with all but the first option and then rerun with only the first option but that won’t always work especially if you are really behind on wsus cleanup. So your options are to reinstall the wsus database or do a manual cleanup. In the past when I had seen issues with this occur I had reinstalled the wsus database but that isn’t really the right solution so instead you can run the below on the wsus db.
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
This will find and delete all the obsolete updates for you and then you can rerun server cleanup and it should finish successfully. Once that is done you should probably either run or schedule wsus cleanup on a regular basis.
I can’t take credit for that sql query though and you can find more info at the below post which I found after I had figured this all out myself. I was doing the same thing to cleanup but his script is a little better/easier to use than mine. Take a look at the below post as it goes through setting wsus cleanup and reindexing on a schedule in very great detail.