WSUS re-indexing and Obsolete updates deletion task

We have to do the WSUS re-indexing and Obsolete updates deletion task against the SUSDB to shrink the database and to bring down the size under control.

run the below script for Re-indexing the SUSDB.

USE SUSDB;

GO

SET NOCOUNT ON;

-- Rebuild or reorganize indexes based on their fragmentation levels

DECLARE @work_to_do TABLE (

    objectid int

    , indexid int

    , pagedensity float

    , fragmentation float

    , numrows int

)

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @schemaname nvarchar(130); 

DECLARE @objectname nvarchar(130); 

DECLARE @indexname nvarchar(130); 

DECLARE @numrows int

DECLARE @density float;

DECLARE @fragmentation float;

DECLARE @command nvarchar(4000); 

DECLARE @fillfactorset bit

DECLARE @numpages int

-- Select indexes that need to be defragmented based on the following

-- * Page density is low

-- * External fragmentation is high in relation to index size

PRINT 'Estimating fragmentation: Begin. ' + convert(nvarchar, getdate(), 121) 

INSERT @work_to_do

SELECT

    f.object_id

    , index_id

    , avg_page_space_used_in_percent

    , avg_fragmentation_in_percent

    , record_count

FROM 

    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') AS f

WHERE

    (f.avg_page_space_used_in_percent < 85.0 and f.avg_page_space_used_in_percent/100.0 * page_count < page_count - 1)

    or (f.page_count > 50 and f.avg_fragmentation_in_percent > 15.0)

    or (f.page_count > 10 and f.avg_fragmentation_in_percent > 80.0)

PRINT 'Number of indexes to rebuild: ' + cast(@@ROWCOUNT as nvarchar(20))

PRINT 'Estimating fragmentation: End. ' + convert(nvarchar, getdate(), 121)

SELECT @numpages = sum(ps.used_page_count)

FROM

    @work_to_do AS fi

    INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id

    INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

-- Declare the cursor for the list of indexes to be processed.

DECLARE curIndexes CURSOR FOR SELECT * FROM @work_to_do

-- Open the cursor.

OPEN curIndexes

-- Loop through the indexes

WHILE (1=1)

BEGIN

    FETCH NEXT FROM curIndexes

    INTO @objectid, @indexid, @density, @fragmentation, @numrows;

    IF @@FETCH_STATUS < 0 BREAK;

    SELECT 

        @objectname = QUOTENAME(o.name)

        , @schemaname = QUOTENAME(s.name)

    FROM 

        sys.objects AS o

        INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id

    WHERE 

        o.object_id = @objectid;

    SELECT 

        @indexname = QUOTENAME(name)

        , @fillfactorset = CASE fill_factor WHEN 0 THEN 0 ELSE 1 END

    FROM 

        sys.indexes

    WHERE

        object_id = @objectid AND index_id = @indexid;

    IF ((@density BETWEEN 75.0 AND 85.0) AND @fillfactorset = 1) OR (@fragmentation < 30.0)

        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

    ELSE IF @numrows >= 5000 AND @fillfactorset = 0

        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD WITH (FILLFACTOR = 90)';

    ELSE

        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

    PRINT convert(nvarchar, getdate(), 121) + N' Executing: ' + @command;

    EXEC (@command);

    PRINT convert(nvarchar, getdate(), 121) + N' Done.';

END

-- Close and deallocate the cursor.

CLOSE curIndexes;

DEALLOCATE curIndexes;

IF EXISTS (SELECT * FROM @work_to_do)

BEGIN

    PRINT 'Estimated number of pages in fragmented indexes: ' + cast(@numpages as nvarchar(20))

    SELECT @numpages = @numpages - sum(ps.used_page_count)

    FROM

        @work_to_do AS fi

        INNER JOIN sys.indexes AS i ON fi.objectid = i.object_id and fi.indexid = i.index_id

        INNER JOIN sys.dm_db_partition_stats AS ps on i.object_id = ps.object_id and i.index_id = ps.index_id

    PRINT 'Estimated number of pages freed: ' + cast(@numpages as nvarchar(20))

END

GO

--Update all statistics

PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121) 

EXEC sp_updatestats

PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121) 

GO

2)    After re-indexing the SUSDB, run the Obsolete Updates Deletion query against the SUSDB to delete the obsolete updates from the SUSDB. This task will take even 1 or 2 days so be patience. 

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

Note:- i)  run these maintenance queries against the SUSDB only and not our SCCM DB.

ii) copy the SUSDB backup to a different location(as back-up) before performing the activity.