SCCM WSUS / SUSDB Maintenance

 

Why is WSUS Maintenance Important?

As WSUS stores metadata, update content, and client communication information, the WSUS database can grow over time. If left unmanaged, this can lead to:

• Slower performance for both WSUS console operations and client update installations.
• Excessive disk usage, leading to storage issues.
• Database fragmentation, which reduces the speed of queries and operations.

To avoid these issues, regular maintenance is necessary.

---

1. Reindexing the WSUS Database

Reindexing helps optimize the performance of the WSUS database by reorganizing and rebuilding indexes. This improves query performance, making it faster to retrieve update information.

Steps to Reindex the WSUS Database:

1. Connect to SQL Server: Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance hosting the WSUS database (SUSDB).
2. Rebuild Indexes:
• Once connected to the database, you can run the following script to rebuild all indexes in 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

 This will update statistics for the WSUS database and rebuild the indexes, improving performance.

2. Updating Database Statistics

Updating statistics in the WSUS database helps SQL Server determine the most efficient way to execute queries. Over time, as data in the database changes, SQL queries can become less efficient if the statistics are outdated.

Steps to Update Database Statistics:

1. Run the Update Statistics Command:
• You can update statistics for the entire WSUS database by running:

--Update all statistics

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

EXEC sp_updatestats

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

GO

This command updates the statistics for all tables in the database, improving the query execution plan used by SQL Server.

2. Schedule Regular Updates:
• To keep the database running efficiently, it’s a good practice to schedule regular updates to statistics. You can automate this by creating a SQL job to run the sp_updatestats procedure at regular intervals.

---

3. Viewing and Managing Updates in WSUS

One of the key tasks in WSUS maintenance is managing the updates available for clients. Regularly reviewing, approving, and declining updates can help ensure that only relevant and required updates are distributed to clients, reducing unnecessary bandwidth and storage usage.

 

Check Decline, superseed & ununsed update count

 

select

--(Select count (*) 'Total Updates' from vwMinimalUpdate ) 'Total Updates',

 

--(Select count (*) 'Live updates'  from vwMinimalUpdate where declined=0) as 'Live Updates',

 

--(Select count (*) 'Superseded'  from vwMinimalUpdate where IsSuperseded =1) as 'Superseded',

 

--(Select count (*) 'Superseded But NoDeclined'  from vwMinimalUpdate where IsSuperseded =1 and declined=0) as 'Superseded but not declined',

 

(Select count (*) 'Declined'  from vwMinimalUpdate where declined=1) as 'Declined',

 

(Select count (*) 'Superseded & Declined' from vwMinimalUpdate where IsSuperseded =1 and declined=1) 'Superseded & Declined'

Query to Decline update superseded 90 Days

DECLARE @thresholdDays INT = 90   -- Specify the number of days between today and the release date for which the superseded updates must not be declined.

DECLARE @testRun BIT = 0          -- Set this to 1 to test without declining anything.

 

-- There shouldn't be any need to modify anything after this.

 

DECLARE @uid UNIQUEIDENTIFIER

DECLARE @title NVARCHAR(500)

DECLARE @date DATETIME

DECLARE @userName NVARCHAR(100) = SYSTEM_USER

 

DECLARE @count INT = 0

 

DECLARE DU CURSOR FOR

       SELECT MU.UpdateID, U.DefaultTitle, U.CreationDate FROM vwMinimalUpdate MU

       JOIN PUBLIC_VIEWS.vUpdate U ON MU.UpdateID = U.UpdateId

       WHERE MU.IsSuperseded = 1 AND MU.Declined = 0 AND MU.IsLatestRevision = 1

       AND MU.CreationDate < DATEADD(dd,-@thresholdDays,GETDATE())

       ORDER BY MU.CreationDate

 

PRINT 'Declining superseded updates older than ' + CONVERT(NVARCHAR(5), @thresholdDays) + ' days.' + CHAR(10)

 

OPEN DU

FETCH NEXT FROM DU INTO @uid, @title, @date

WHILE (@@FETCH_STATUS > - 1)

BEGIN 

       SET @count = @count + 1

       PRINT 'Declining update ' + CONVERT(NVARCHAR(50), @uid) + ' (Creation Date ' + CONVERT(NVARCHAR(50), @date) + ') - ' + @title + ' ...'

       IF @testRun = 0

              EXEC spDeclineUpdate @updateID = @uid, @adminName = @userName, @failIfReplica = 1

 

       FETCH NEXT FROM DU INTO @uid, @title, @date

END

 

CLOSE DU

DEALLOCATE DU

 

PRINT CHAR(10) + 'Attempted to decline ' + CONVERT(NVARCHAR(10), @count) + ' updates.'

WSUS delete old update

DECLARE @UpdateID VARCHAR(50)

DECLARE @msg nvarchar(100)

DECLARE Curs CURSOR FOR

      SELECT UpdateID FROM [SUSDB].[PUBLIC_VIEWS].[vUpdate] where IsDeclined=1

 

OPEN Curs FETCH NEXT FROM Curs into @UpdateID

WHILE @@FETCH_STATUS = 0

BEGIN

SET @msg = 'Deleting' + CONVERT(varchar(100), @UpdateID)

RAISERROR(@msg,0,1) WITH NOWAIT

exec spDeleteUpdateByUpdateID @UpdateID FETCH NEXT FROM Curs into @UpdateID

 

END

CLOSE Curs

DEALLOCATE Curs

 

Conclusion

Regular maintenance of your WSUS environment is essential for optimal performance and reliability. By reindexing the database, updating statistics, managing update approvals, and cleaning up old records, you can ensure that WSUS remains efficient and capable of distributing updates in a timely manner.

Automating these tasks using SQL jobs or PowerShell scripts will help keep WSUS running smoothly without requiring constant manual intervention.