[WSUS] WSUS Server Cleanup Wizard가 안될 때 Manual로 수행하는 방법

WSUS 최적화를 위해서는 WSUS Console에 있는 Server Cleanup Wizard를 사용하지만, 경우에 따라서 서버 정리 마법사가 업데이트 콘솔에서 정상적으로 수행되지 않는 상황일 경우 아래 방법으로 수행해야 한다.


1. 아래 Powershell 스크립트를 복붙으로 생성하여 관리자 권한으로 Manually 정리작업을 진행한다. 

[Powershell Script for WSUS Cleanup]

[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")` | out-null $wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer(); $cleanupScope = new-object Microsoft.UpdateServices.Administration.CleanupScope; $cleanupScope.DeclineSupersededUpdates = $true        $cleanupScope.DeclineExpiredUpdates         = $true $cleanupScope.CleanupObsoleteUpdates     = $true $cleanupScope.CompressUpdates                  = $true #$cleanupScope.CleanupObsoleteComputers = $true $cleanupScope.CleanupUnneededContentFiles = $true $cleanupManager = $wsus.GetCleanupManager(); $cleanupManager.PerformCleanup($cleanupScope); 

2. WSUS DB index Rebuild 작업을 통해서 DB 응답속도 향상 및 Connection Timeout 문제가 생기는 것을 방지한다


1) WSUS DB의 연결 이름을 확인합니다. (하기 음영 내용 참조)

2) 관리자 권한의 cmd창에서 다음의 명령어를 통해서 Index Rebuild 쿼리문을 실행합니다.

• sqlcmd가 없을 경우 다운로드 

/******************************************************************************
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

--Update all statistics
PRINT 'Updating all statistics.' + convert(nvarchar, getdate(), 121)
EXEC sp_updatestats
PRINT 'Done updating statistics.' + convert(nvarchar, getdate(), 121)
GO

• 관리자 권한의 cmd 창에서 다음의 명령어를 입력합니다.
  sqlcmd -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query –i <scriptLocation>\WsusDBMaintenance2.sql
• 밑줄 표기 : 1)번에서 확인한 서버이름
• <Script Location> : 첨부된 Query파일 다운로드 경로
• # 커맨드 입력 예시

3) 실행결과 예시는 다음과 같습니다