Reindex WSUS Database on Windows Server 2016

Neil Klawitter

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 ''.

momurda

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.

Neil Klawitter

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.

DustinB3403

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

DustinB3403

@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.

scottalanmiller

@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.

momurda

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

JaredBusch

@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.

Neil Klawitter

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

Neil Klawitter

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

scottalanmiller

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

Neil Klawitter

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.