Reindex WSUS Database on Windows Server 2016



  • Does anyone know how to reindex the WSUS Windows internal database on Server 2016? I have tried the Microsoft recommended reindexing SQL running it with SQLCMD, but have been unsuccessful. The closest I got was it brings up the SQL prompt 1>, with an error of: Msg 102, Level 15, State 1, Server XXX-WSUS\MICROSOFT##WID, Line 1. Incorrect syntax near ''.



  • The error message just stops after the word "near "? You've got some sort of typo or missing some bracket somewhere.

    It probably is easier to uninstall wsus role, delete the db, then reinstall wsus role than to try and fix a broken wsus db, if it is broken.



  • It was "Incorrect syntax near''." I just built this server, and it works just fine so I don't want to rebuild it. I just need to do the recommended monthly database reindexing.



  • Yeah I'd honestly just rebuild it, who has time to fuss around with WSUS sql db. . .



  • @neil-klawitter said in Reindex WSUS Database on Windows Server 2016:

    It was "Incorrect syntax near''." I just built this server, and it works just fine so I don't want to rebuild it. I just need to do the recommended monthly database reindexing.

    Rebuilding the WSUS role is simply removing the role and the database and restarting, then installing the role again.



  • @momurda said in Reindex WSUS Database on Windows Server 2016:

    The error message just stops after the word "near "?

    No, it is telling him the error is near a double quote mark.



  • @neil-klawitter Where did you see this monthly index rebuilding recommendation? What is the query youre using?



  • @scottalanmiller said in Reindex WSUS Database on Windows Server 2016:

    @momurda said in Reindex WSUS Database on Windows Server 2016:

    The error message just stops after the word "near "?

    No, it is telling him the error is near a double quote mark.

    Actually, because it is SQL, that is likely two single quotes representing an empty string.



  • The last part of the error is actually: Incorrect syntax near 'backslash'. For some reason the backslash is not displaying on the post.



  • I have a batch file that runs sqlcmd that looks like this:

    "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\sqlcmd" -S "np:\\.\pipe\MICROSOFT##WID\tsql\query" -I -q "C:\Scripts\WSUSDBMaintenance.sql"
    
    and the WSUSDBMaintenance.sql file looks like this:
    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
    


  • @neil-klawitter I edited to mark up as code.



  • I finally got this to work if anyone is interested. The sql file is fine, but I had to change my batch file. The correct one looks like this:

    "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\sqlcmd" -I -S \.\pipe\MICROSOFT##WID\tsql\query -i C:\Scripts\WSUSDBMaintenance.sql

    It must be run from an Administrator command prompt. This took me several days to figure out so I hope it can benefit someone else. Everything I found about reindexing the WSUS database was on Server 2008 or 2012. This works on Server 2016.