question

tshiori3701 avatar image
0 Votes"
tshiori3701 asked RitaHu-MSFT answered

Re-index the WSUS 3.0 Database

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://docs.microsoft.com/ja-jp/archive/blogs/jpwsus/wsus-db

sql-server-generalwindows-server-update-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered tshiori3701 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you so much for your comments.
I could not access the link you shown... so, if you can, could you tell me the script title? i will search it on Git hub.

0 Votes 0 ·
RitaHu-MSFT avatar image
0 Votes"
RitaHu-MSFT answered OlafHelper-2800 commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you so much for your answer.
WSUS server is WIndows server 2012R2. As you saying, "Re-index the WSUS 3.0 Database" may not be
Appropriate for WIndows server 2012R2.
if you have similar script as "Re-index the WSUS 3.0 Database" for WIndows server 2012R2, I am happy to hear that.

0 Votes 0 ·

That SQL script uses DMV's to get index fragmentation and rebuild fragmented indexes; that's nothing WSUS specific or related on the WSUS version; you can run that script on any database.

0 Votes 0 ·
RitaHu-MSFT avatar image
0 Votes"
RitaHu-MSFT answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.