WSUS DB Issue to delete obsolete update

Symptom:
replication issue from Primary to CAS.

Cause:
Lack of resources on server

Resolution:
Ran script on SUS DB for deleting obsolete updates:-

===========================

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

===================

 

Reindex SUSDB:- Re-indexing of WSUS DB is a recommended task to maintain its performance.

Rebuilds all the indexes in a database:-

Exec sp_MSForEachtable 'DBCC DBREINDEX (''?'')'

Go

 

To update statstics:-

exec sp_msforeachtable 'update statistics ? with fullscan'

Go

 

 

Reference Articles:- http://blogs.technet.com/b/configurationmgr/archive/2016/01/26/the-complete-guide-to-microsoft-wsus-and-configuration-manager-sup-maintenance.aspx

 

Script for declining superseded updates:-

http://blogs.technet.com/b/configurationmgr/archive/2015/04/15/support-tip-configmgr-2012-update-scan-fails-and-causes-incorrect-compliance-status.aspx