Create custom indexes for WSUS Database
The following steps should be used to create custom indexes in the SUSDB database. This is a one-time process, which is optional but recommended, as doing so will greatly improve performance during subsequent cleanup operations.
- Ensure you have a backup of the SUSDB database.
- Use SQL Management Studio to connect to the SUSDB database, in the same manner as described in the Re-index the WSUS database section below.
- Run the following script against SUSDB, to create two custom indexes:
-- Create custom index in tbLocalizedPropertyForRevision
USE [SUSDB]
CREATE NONCLUSTERED INDEX [nclLocalizedPropertyID] ON [dbo].[tbLocalizedPropertyForRevision]
(
[LocalizedPropertyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
-- Create custom index in tbRevisionSupersedesUpdate
CREATE NONCLUSTERED INDEX [nclSupercededUpdateID] ON [dbo].[tbRevisionSupersedesUpdate]
(
[SupersededUpdateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Re-index the WSUS database
The steps to connect to SUSDB and perform the re-index differ, depending on whether SUSDB is running in SQL Server or Windows Internal Database (WID). To determine where SUSDB is running, check the registry key on the WSUS server located at HKLM\Software\Microsoft\Update Services\Server\Setup, and the SQLServerName value.
If the value contains just the server name or server\instance, SUSDB is running on a SQL Server. If the value includes the string ##SSEE or ##WID in it, SUSDB is running in Windows Internal Database, as shown:
If SUSDB was installed on Windows Internal Database (WID)
If SUSDB was installed on WID, SQL Management Studio Express must be installed locally in order to run the re-index script. Here’s an easy way to determine which version of SQL Server Management Studio Express to install:
- For Windows Server 2012 or newer:
- Go to C:\Windows\WID\Log and find the error log that contains the version number.
- Look up the version number in the following article. This will tell you what Service Pack level it is running. Include the SP level when searching the Microsoft Download Center for SQL Management Studio Express, as it does sometimes matter.
- For Windows Server 2008 R2 or older:
- Go to C:\Windows\SYSMSI\SSEE\MSSQL.2005\MSSQL\LOG and open up the last error log with Notepad. At the very top there will be a version number (e.g. 9.00.4035.00 x64). Look up the version number in the following article. This will tell you what Service Pack level it is running. Include the SP level when searching the Microsoft Download Center for SQL Management Studio Express:
After installing SQL Management Studio Express, launch it, and enter the server name to connect to:
- If the OS is Windows Server 2012 or newer, use \\.\pipe\MICROSOFT##WID\tsql\query
- If the OS is older than Windows Server 2012, enter \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
If SUSDB was installed on SQL Server
If SUSDB was installed on full SQL Server, simply launch SQL Server Management Studio and enter the name of the server (and instance if needed) when prompted.
Running the script
To run the script in either SQL Server Management Studio or SQL Server Management Studio Express, click on the New Query button, paste the script in the window and then click Execute. When it is finished, a Query executed successfully message will be displayed in the status bar, and the Results pane will contain messages related to what indexes were rebuilt.
Script:
/******************************************************************************
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)
GODecline superseded updates
Decline superseded updates in the WSUS server to help clients scan more efficiently. Before declining updates, ensure that the superseding updates are deployed, and that superseded ones are no longer needed. Configuration Manager includes a separate cleanup, which allows it to expire superseded updates based on specified criteria.
Superseded updates can be manually declined via the WSUS console, or you can run this PowerShell script:
# ===============================================
# Script to decline superseeded updates in WSUS.
# ===============================================
# It's recommended to run the script with the -SkipDecline switch to see how many superseded updates are in WSUS and to TAKE A BACKUP OF THE SUSDB before declining the updates.
# Parameters:
# $UpdateServer = Specify WSUS Server Name
# $UseSSL = Specify whether WSUS Server is configured to use SSL
# $Port = Specify WSUS Server Port
# $SkipDecline = Specify this to do a test run and get a summary of how many superseded updates we have
# $DeclineLastLevelOnly = Specify whether to decline all superseded updates or only last level superseded updates
# $ExclusionPeriod = Specify the number of days between today and the release date for which the superseded updates must not be declined. Eg, if you want to keep superseded updates published within the last 2 months, specify a value of 60 (days)
# Supersedence chain could have multiple updates.
# For example, Update1 supersedes Update2. Update2 supersedes Update3. In this scenario, the Last Level in the supersedence chain is Update3.
# To decline only the last level updates in the supersedence chain, specify the DeclineLastLevelOnly switch
# Usage:
# =======
# To do a test run against WSUS Server without SSL
# Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -Port 8530 -SkipDecline
# To do a test run against WSUS Server using SSL
# Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -UseSSL -Port 8531 -SkipDecline
# To decline all superseded updates on the WSUS Server using SSL
# Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -UseSSL -Port 8531
# To decline only Last Level superseded updates on the WSUS Server using SSL
# Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -UseSSL -Port 8531 -DeclineLastLevelOnly
# To decline all superseded updates on the WSUS Server using SSL but keep superseded updates published within the last 2 months (60 days)
# Decline-SupersededUpdates.ps1 -UpdateServer SERVERNAME -UseSSL -Port 8531 -ExclusionPeriod 60
[CmdletBinding()]
Param(
[Parameter(Mandatory=$True,Position=1)]
[string] $UpdateServer,
[Parameter(Mandatory=$False)]
[switch] $UseSSL,
[Parameter(Mandatory=$True, Position=2)]
$Port,
[switch] $SkipDecline,
[switch] $DeclineLastLevelOnly,
[Parameter(Mandatory=$False)]
[int] $ExclusionPeriod = 0
)
Write-Host ""
if ($SkipDecline -and $DeclineLastLevelOnly) {
Write-Host "Using SkipDecline and DeclineLastLevelOnly switches together is not allowed."
Write-Host ""
return
}
$outPath = Split-Path $script:MyInvocation.MyCommand.Path
$outSupersededList = Join-Path $outPath "SupersededUpdates.csv"
$outSupersededListBackup = Join-Path $outPath "SupersededUpdatesBackup.csv"
"UpdateID, RevisionNumber, Title, KBArticle, SecurityBulletin, LastLevel" | Out-File $outSupersededList
try {
if ($UseSSL) {
Write-Host "Connecting to WSUS server $UpdateServer on Port $Port using SSL... " -NoNewLine
} Else {
Write-Host "Connecting to WSUS server $UpdateServer on Port $Port... " -NoNewLine
}
[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | out-null
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer($UpdateServer, $UseSSL, $Port);
}
catch [System.Exception]
{
Write-Host "Failed to connect."
Write-Host "Error:" $_.Exception.Message
Write-Host "Please make sure that WSUS Admin Console is installed on this machine"
Write-Host ""
$wsus = $null
}
if ($wsus -eq $null) { return }
Write-Host "Connected."
$countAllUpdates = 0
$countSupersededAll = 0
$countSupersededLastLevel = 0
$countSupersededExclusionPeriod = 0
$countSupersededLastLevelExclusionPeriod = 0
$countDeclined = 0
Write-Host "Getting a list of all updates... " -NoNewLine
try {
$allUpdates = $wsus.GetUpdates()
}
catch [System.Exception]
{
Write-Host "Failed to get updates."
Write-Host "Error:" $_.Exception.Message
Write-Host "If this operation timed out, please decline the superseded updates from the WSUS Console manually."
Write-Host ""
return
}
Write-Host "Done"
Write-Host "Parsing the list of updates... " -NoNewLine
foreach($update in $allUpdates) {
$countAllUpdates++
if ($update.IsDeclined) {
$countDeclined++
}
if (!$update.IsDeclined -and $update.IsSuperseded) {
$countSupersededAll++
if (!$update.HasSupersededUpdates) {
$countSupersededLastLevel++
}
if ($update.CreationDate -lt (get-date).AddDays(-$ExclusionPeriod)) {
$countSupersededExclusionPeriod++
if (!$update.HasSupersededUpdates) {
$countSupersededLastLevelExclusionPeriod++
}
}
"$($update.Id.UpdateId.Guid), $($update.Id.RevisionNumber), $($update.Title), $($update.KnowledgeBaseArticles), $($update.SecurityBulletins), $($update.HasSupersededUpdates)" | Out-File $outSupersededList -Append
}
}
Write-Host "Done."
Write-Host "List of superseded updates: $outSupersededList"
Write-Host ""
Write-Host "Summary:"
Write-Host "========"
Write-Host "All Updates =" $countAllUpdates
Write-Host "Any except Declined =" ($countAllUpdates - $countDeclined)
Write-Host "All Superseded Updates =" $countSupersededAll
Write-Host " Superseded Updates (Intermediate) =" ($countSupersededAll - $countSupersededLastLevel)
Write-Host " Superseded Updates (Last Level) =" $countSupersededLastLevel
Write-Host " Superseded Updates (Older than $ExclusionPeriod days) =" $countSupersededExclusionPeriod
Write-Host " Superseded Updates (Last Level Older than $ExclusionPeriod days) =" $countSupersededLastLevelExclusionPeriod
Write-Host ""
$i = 0
if (!$SkipDecline) {
Write-Host "SkipDecline flag is set to $SkipDecline. Continuing with declining updates"
$updatesDeclined = 0
if ($DeclineLastLevelOnly) {
Write-Host " DeclineLastLevel is set to True. Only declining last level superseded updates."
foreach ($update in $allUpdates) {
if (!$update.IsDeclined -and $update.IsSuperseded -and !$update.HasSupersededUpdates) {
if ($update.CreationDate -lt (get-date).AddDays(-$ExclusionPeriod)) {
$i++
$percentComplete = "{0:N2}" -f (($updatesDeclined/$countSupersededLastLevelExclusionPeriod) * 100)
Write-Progress -Activity "Declining Updates" -Status "Declining update #$i/$countSupersededLastLevelExclusionPeriod - $($update.Id.UpdateId.Guid)" -PercentComplete $percentComplete -CurrentOperation "$($percentComplete)% complete"
try
{
$update.Decline()
$updatesDeclined++
}
catch [System.Exception]
{
Write-Host "Failed to decline update $($update.Id.UpdateId.Guid). Error:" $_.Exception.Message
}
}
}
}
}
else {
Write-Host " DeclineLastLevel is set to False. Declining all superseded updates."
foreach ($update in $allUpdates) {
if (!$update.IsDeclined -and $update.IsSuperseded) {
if ($update.CreationDate -lt (get-date).AddDays(-$ExclusionPeriod)) {
$i++
$percentComplete = "{0:N2}" -f (($updatesDeclined/$countSupersededAll) * 100)
Write-Progress -Activity "Declining Updates" -Status "Declining update #$i/$countSupersededAll - $($update.Id.UpdateId.Guid)" -PercentComplete $percentComplete -CurrentOperation "$($percentComplete)% complete"
try
{
$update.Decline()
$updatesDeclined++
}
catch [System.Exception]
{
Write-Host "Failed to decline update $($update.Id.UpdateId.Guid). Error:" $_.Exception.Message
}
}
}
}
}
Write-Host " Declined $updatesDeclined updates."
if ($updatesDeclined -ne 0) {
Copy-Item -Path $outSupersededList -Destination $outSupersededListBackup -Force
Write-Host " Backed up list of superseded updates to $outSupersededListBackup"
}
}
else {
Write-Host "SkipDecline flag is set to $SkipDecline. Skipped declining updates"
}
Write-Host ""
Write-Host "Done"
Write-Host ""
The following command lines illustrate the various ways that the PS script can be run (if the script is being run on the WSUS server, LOCALHOST can be used in place of the actual SERVERNAME):
Decline-SupersededUpdatesWithExclusionPeriod.ps1 -UpdateServer SERVERNAME -Port 8530 –SkipDecline
Decline-SupersededUpdatesWithExclusionPeriod.ps1 -UpdateServer SERVERNAME -Port 8530 –ExclusionPeriod 60
Decline-SupersededUpdatesWithExclusionPeriod.ps1 -UpdateServer SERVERNAME -Port 8530
Decline-SupersededUpdatesWithExclusionPeriod.ps1 -UpdateServer SERVERNAME -UseSSL -Port 8531Running the script with a –SkipDecline and –ExclusionPeriod 60 to gather information about updates on the WSUS server, and how many updates could be declined:
Running the script with –ExclusionPeriod 60, to decline superseded updates older than 60 days:
Output and progress indicator is displayed while the script is running. Note the SupersededUpdates.csv file, which will contain a list of all updates which are declined by the script: