WSUS Cleanup script. (when mmc still fails)

The script is not mine.I found it as is on the web.It is the ONLY script that actually does the work when you are dealing with broken MMC console sessions.
Tested on a 1.2 TB WSUS installation on a windows Server 2012 R2.

<# WSUS-CLEANUP-UPDATES Runs WSUS cleanup task using stored procedures in WSUS database thus avoiding timeout errors that may occur when running WSUS Cleanup Wizard. The script is intended to run as a scheduled task on WSUS server but can also be used remotely. $SqlServer and $SqlDB variables must be defined before running the script on a server without WSUS. Version 4 Version history: 4 Added database connection state check before deleting an unused update: the script will now attempt to reestablish connection if broken. #>

##########################
# Configurable parameters

$SqlServer = “” # SQL server host name; leave empty to use information from local registry
$SqlDB = “SUSDB” # WSUS database name
$SkipFileCleanup = $SqlServer -ne “”

$log_source = “WSUS cleanup Task” # Event log source name
$log_debugMode = $true # set to false to suppress console output

##########################

$ErrorActionPreference = “Stop”

# basic logging facility

function log_init{
if ( -not [System.Diagnostics.EventLog]::SourceExists($log_source) ){
[System.Diagnostics.EventLog]::CreateEventSource($log_source, “Application”)
}
}

function log( [string] $msg, [int32] $eventID, [System.Diagnostics.EventLogEntryType] $level ){
Write-EventLog -LogName Application -Source $log_source -EntryType $level -EventId $eventID -Message $msg
if ( $log_debugMode ){
switch ($level){
Warning {Write-Host $msg -ForegroundColor Yellow }
Error { Write-Host $msg -ForegroundColor Red }
default { Write-Host $msg -ForegroundColor Gray }
}
}
}

function dbg( [string] $msg ){
if ( $log_debugMode ){
log “DBG: $msg” 300 “Information”
}
}

log_init

#########################

function DeclineExpiredUpdates( $dbconn ){

log “Declining expired updates” 1 “Information”

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $dbconn
$Command.CommandTimeout = 3600
$Command.CommandText = “EXEC spDeclineExpiredUpdates”
try{
$Command.ExecuteNonQuery() | Out-Null
}
catch{
$script:errorCount++
log “Exception declining expired updates:`n$_” 99 “Error”
}
}

#########################

function DeclineSupersededUpdates( $dbconn ){

log “Declining superseded updates” 1 “Information”

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $dbconn
$Command.CommandTimeout = 1800
$Command.CommandText = “EXEC spDeclineSupersededUpdates”
try{
$Command.ExecuteNonQuery() | Out-Null
}
catch{
$script:errorCount++
log “Exception declining superseded updates:`n$_” 99 “Error”
}
}

#######################

function DeleteObsoleteUpdates( $dbconn ){

Log “Reading obsolete update list.” 1 “Information”
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $dbconn
$Command.CommandTimeout = 600
$Command.CommandText = “EXEC spGetObsoleteUpdatesToCleanup”
$reader = $Command.ExecuteReader()
$table = New-Object System.Data.DataTable
$table.Load($reader)

$updatesTotal = $table.Rows.Count
log “Found $updatesTotal updates that can be deleted.” 1 “Information”

$updatesProcessed=0
$Command.CommandTimeout = 300
foreach( $row in $table.Rows ){
try{
if ( $dbconn.State -ne [System.Data.ConnectionState]::Open ){
log “Re-opening database connection” 2 “Warning”
$dbconn.Open()
}
$updatesProcessed++
log “Deleting update $($row.localUpdateID) ($updatesProcessed of $updatesTotal)” 1 “Information”
$Command.CommandText = “exec spDeleteUpdate @LocalUpdateID=$($row.localUpdateID)”
$Command.ExecuteNonQuery() | Out-Null
}
catch{
$errorCount++
log “Error deleting update $($row.localUpdateID):`n$_” 8 “Warning”
}
}

}

###################

function DbConnectionString{

$WsusSetupKey = “HKLM:\SOFTWARE\Microsoft\Update Services\Server\Setup”

if ( $script:SqlServer -eq “” ){
$server = Get-ItemProperty -path $WsusSetupKey -Name “SqlServerName” -ErrorAction SilentlyContinue
$db = Get-ItemProperty -path $WsusSetupKey -Name “SqlDatabaseName” -ErrorAction SilentlyContinue
if ( ! $server ){
throw “Cannot determine SQL server name”
}
$script:SqlServer = $server.SqlServerName
$script:SqlDB = $db.SqlDatabaseName
}

if ( $script:SqlServer -match “microsoft##” ){
return “data source=\\.\pipe\$script:SqlServer\tsql\query;Integrated Security=True;database=’$script:SqlDB’;Network Library=dbnmpntw”
}
else{
return “server=’$script:SqlServer’;database=’$script:SqlDB’;trusted_connection=true;”
}

}

##############

function DeleteUnusedContent{

log “Deleting unneeded content files” 1 “Information”

try{
Import-Module UpdateServices
$status = Invoke-WsusServerCleanup -CleanupUnneededContentFiles
log “Done deleting unneeded content files: $status” 1 “Information”
}
catch{
$script:errorCount++
log “Exception deleting unneeded content files:`n$_” 99 “Error”
}

}

###################

function DeleteInactiveComputers( $DbConn ){

log “Removing obsolete computers” 1 “Information”

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $dbconn
$Command.CommandTimeout = 1800
$Command.CommandText = “EXEC spCleanupObsoleteComputers”
try{
$Command.ExecuteNonQuery() | Out-Null
}
catch{
$script:errorCount++
log “Exception removing obsolete computers:`n$_” 99 “Error”
}

}

function RestartWsusService{
log “Stopping IIS..” 1 “Information”
try{
Stop-Service W3SVC -Force
try{
log “Restarting WSUS service..” 1 “Information”
Restart-Service WsusService -Force
}
finally{
log “Starting IIS…” 1 “Information”
Start-Service W3SVC
}
}
catch{
$script:errorCount++
log “Error restarting WSUS services:`n$_” 99 “Error”
}
Start-Sleep -Seconds 30
}

<#------------------------------------------------ MAIN -------------------------------------------------#>

$timeExecStart = Get-Date
$errorCount = 0

try{

$Conn = New-Object System.Data.SQLClient.SQLConnection
$Conn.ConnectionString = DbConnectionString
log “Connecting to database $SqlDB on $SqlServer” 1 “Information”
$Conn.Open()
try{
DeclineExpiredUpdates $Conn
DeclineSupersededUpdates $Conn
DeleteObsoleteUpdates $Conn
DeleteInactiveComputers $Conn
RestartWsusService
if ( ! $SkipFileCleanup ) {
DeleteUnusedContent
}
}
finally{
$Conn.Close()
}

}
catch{
$errorCount++
log “Unhandled exception:`n$_” 100 “Error”
}

$time_exec = ( Get-Date ) – $timeExecStart
log “Completed script execution with $errorCount error(s)`nExecution time $([math]::Round($time_exec.TotalHours)) hours and $([math]::Round($time_exec.totalMinutes)) minutes.” 1 “Information”

Leave a Reply

Your email address will not be published. Required fields are marked *