Re-index the WSUS 3.0 Database

Question

Good morning.
I accessed the follwowing URL, but I could not find Re-index the WSUS 3.0 Database script.
Before, there is it.
Could you show me where is it now?
Re-index the WSUS 3.0 Database
https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61

Reference:https://learn.microsoft.com/ja-jp/archive/blogs/jpwsus/wsus-db

Answer

Hi TosakiShioriABS-0371,

Thanks for your posting on Q&A.

In my opinion, the WSUS 3.0 is installed in the Windows Server 2008. Am I right?

It is too old to installed the WSUS role in the Windows Server 2008/2008R2. It is recommended to install the WSUS role in the Windows Server 2012R2 and higher version.
I'm not sure whether the script is suitable for the Windows Server 2008 and I don't have a related environment to test.

Reference link: https://gist.github.com/emnavarro02/0ffd6481ce7c9b207f7762732fd73aa8

Thanks for your time.

Regards,
Rita

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

Hi tshiori3701,

I have saved this script before. So I want to share with you and I attached below.

This sample T-SQL script performs basic maintenance tasks on SUSDB  
1. Identifies indexes that are fragmented and defragments them. For certain  
   tables, a fill-factor is set in order to improve insert performance.  
   Based on MSDN sample at http://msdn2.microsoft.com/en-us/library/ms188917.aspx  
   and tailored for SUSDB requirements  
2. Updates potentially out-of-date table statistics.  
******************************************************************************/  
  
   
  
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

Regards,
Rita

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

TechNet Gallery is closed, but some of the scripts can be found at GitHub, may this one is the script you are looking for:

https://gist.github.com/emnavarro02/0ffd6481ce7c9b207f7762732fd73aa8

Re-index the WSUS 3.0 Database

3 answers