Re-index the WSUS 3.0 Database

T,Shiori 1 Reputation point
2021-01-29T09:26:38.213+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,883 questions
Windows Server
Windows Server
A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.
12,272 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Rita Hu -MSFT 9,626 Reputation points
    2021-02-01T03:25:13.797+00:00

    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.

    1 person found this answer helpful.

  2. Rita Hu -MSFT 9,626 Reputation points
    2021-02-03T06:59:28.37+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  3. Olaf Helper 41,101 Reputation points
    2021-01-29T10:30:34.317+00:00

    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