VBO Wiki

2. WSUS Maintenance

2 min read

RU | EN | DE

Database

SQLCMD

New-Item -Path C:\Temp -ItemType Directory -Force
New-Item -Path C:\Tools\sqlcmd -ItemType Directory -Force
Invoke-WebRequest -UseBasicParsing -Uri "LINK_ZIP" -OutFile "C:\Temp\sqlcmd.zip"
Expand-Archive -LiteralPath "C:\Temp\sqlcmd.zip" -DestinationPath "C:\Tools\sqlcmd" -Force
Get-ChildItem C:\Tools\sqlcmd -Recurse
C:\Tools\sqlcmd\sqlcmd.exe -?

Backup Databse

cd C:\Tools\sqlcmd\
New-Item -Path C:\WSUS-Backup -ItemType Directory -Force
 
.\sqlcmd.exe -S \\.\pipe\MICROSOFT##WID\tsql\query -E -Q "BACKUP DATABASE [SUSDB] TO DISK = N'C:\WSUS-Backup\SUSDB_full_20260410.bak' WITH COPY_ONLY, INIT, STATS = 10;"
 
.\sqlcmd.exe -S \\.\pipe\MICROSOFT##WID\tsql\query -E -Q "RESTORE VERIFYONLY FROM DISK = N'C:\WSUS-Backup\SUSDB_full_20260410.bak';"

Create Script

@"
USE [SUSDB]
SET NOCOUNT ON
 
DECLARE @work_to_do TABLE (
    objectid INT,
    indexid INT,
    pagedensity FLOAT,
    fragmentation FLOAT,
    numrows INT,
    action_taken NVARCHAR(10)
)
 
INSERT INTO @work_to_do
SELECT
    ps.object_id,
    ps.index_id,
    ps.avg_page_space_used_in_percent,
    ps.avg_fragmentation_in_percent,
    ps.record_count,
    NULL
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ps
WHERE ps.index_id > 0
  AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
 
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @schema_name SYSNAME
DECLARE @object_name SYSNAME
DECLARE @index_name SYSNAME
DECLARE @avg_fragmentation_in_percent FLOAT
DECLARE @avg_page_space_used_in_percent FLOAT
DECLARE @sql NVARCHAR(4000)
 
WHILE EXISTS (SELECT 1 FROM @work_to_do WHERE action_taken IS NULL)
BEGIN
    SELECT TOP 1
        @objectid = objectid,
        @indexid = indexid,
        @avg_fragmentation_in_percent = fragmentation,
        @avg_page_space_used_in_percent = pagedensity
    FROM @work_to_do
    WHERE action_taken IS NULL
    ORDER BY fragmentation DESC
 
    SELECT
        @schema_name = s.name,
        @object_name = o.name
    FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    WHERE o.object_id = @objectid
 
    SELECT @index_name = name
    FROM sys.indexes
    WHERE object_id = @objectid
      AND index_id = @indexid
 
    SET @sql = NULL
 
    IF @avg_fragmentation_in_percent >= 30
        SET @sql = N'ALTER INDEX [' + @index_name + N'] ON [' + @schema_name + N'].[' + @object_name + N'] REBUILD'
    ELSE IF @avg_fragmentation_in_percent >= 5
        SET @sql = N'ALTER INDEX [' + @index_name + N'] ON [' + @schema_name + N'].[' + @object_name + N'] REORGANIZE'
 
    IF @sql IS NOT NULL
    BEGIN
        PRINT @sql
        EXEC sp_executesql @sql
        UPDATE @work_to_do
        SET action_taken = CASE
            WHEN @avg_fragmentation_in_percent >= 30 THEN 'REBUILD'
            WHEN @avg_fragmentation_in_percent >= 5 THEN 'REORG'
        END
        WHERE objectid = @objectid AND indexid = @indexid
    END
    ELSE
    BEGIN
        UPDATE @work_to_do
        SET action_taken = 'NONE'
        WHERE objectid = @objectid AND indexid = @indexid
    END
END
 
EXEC sp_updatestats
"@ | Set-Content -Path C:\WSUS-Backup\SUSDB_Reindex.sql -Encoding ASCII

Reindex DataBase

.\sqlcmd -S \\.\pipe\MICROSOFT##WID\tsql\query -E -i C:\WSUS-Backup\SUSDB_Reindex.sql -o C:\WSUS-Backup\SUSDB_Reindex.log

Log

Get-Content C:\WSUS-Backup\SUSDB_Reindex.log

Clean

WSUS Console → Options → Server Cleanup Wizard:

  • Unused updates and update revisions
  • Older updates and update revisions that haven’t been approved
  • Decline expired updates
  • Decline superseded updates

Sync WSUS

WSUS Console ⇒ Update Services ⇒ ⇒ “Sync Now”

Backlinks