#Requires -Version 5.1
<#
    JbTecWiz Support Centre -- generated fix script

    Fault : The WSUS console crashes, or clients stop reporting
    Fix   : Raise the pool limit, then clean up the database
    Source: https://jbtecwiz.com/support/srv-app-wsus-console

    Run as     : Elevated PowerShell
    Expect     : 2-6 hours including cleanup
    Risk       : medium
    Reversible : yes

    WHEN THIS IS THE RIGHT FIX
    The console crashes. Do both parts -- the limit alone only postpones it.

    HOW TO UNDO IT
    Restore the previous privateMemory value. Declined updates can be
    approved again if needed.

    This script walks the fix one step at a time and asks before each
    one. Steps with no command are things you do yourself -- it prints
    those and waits. Run with -DryRun to print without executing.

    --------------------------------------------------------------------
    NO WARRANTY - USE AT YOUR OWN RISK

    This script is provided by JbTecWiz as-is and with no warranty of any
    kind, express or implied. You run it entirely at your own risk.

    JbTecWiz accepts no liability for any loss or damage arising from its
    use, including but not limited to data loss, downtime, or configuration
    changes that turn out to be wrong for your system.

    You are responsible for reading this script before running it, for
    satisfying yourself that it suits the machine in front of you, and for
    having a working backup first. Some steps cannot be undone.
    --------------------------------------------------------------------
#>

[CmdletBinding()]
param(
    # Print every step and command without running anything.
    [switch]$DryRun,
    # Do not ask before each step. Read the script first if you use this.
    [switch]$Unattended
)

$ErrorActionPreference = 'Stop'
$script:Failed = 0

function Write-Rule { param([string]$Text)
    Write-Host ''
    Write-Host ('-' * 70) -ForegroundColor DarkGray
    if ($Text) { Write-Host $Text -ForegroundColor Cyan }
}

function Show-Prose { param([string]$Text, [string]$Colour = "Gray")
    if (-not $Text) { return }
    $words = $Text -split "\s+"; $line = "  "
    foreach ($w in $words) {
        if (($line.Length + $w.Length) -gt 74) { Write-Host $line -ForegroundColor $Colour; $line = "  " }
        $line += "$w "
    }
    if ($line.Trim()) { Write-Host $line -ForegroundColor $Colour }
}

function Invoke-Step {
    param(
        [int]$Number, [string]$Do, [string]$Why,
        [scriptblock]$Command, [switch]$Manual, [string]$Shell = "powershell"
    )
    Write-Rule "  Step $Number of 6"
    Show-Prose $Do "White"
    if ($Why) { Write-Host ""; Show-Prose $Why "DarkGray" }

    if ($Manual) {
        Write-Host ''
        Write-Host '  -> Do this yourself, then press Enter to carry on.' -ForegroundColor Yellow
        if (-not $Unattended -and -not $DryRun) { [void](Read-Host) }
        return
    }

    Write-Host ''
    foreach ($l in ($Command.ToString().Trim() -split "`n")) {
        Write-Host ("    " + $l.Trim()) -ForegroundColor Green
    }
    Write-Host ''

    if ($DryRun) { Write-Host "  (dry run -- not executed)" -ForegroundColor DarkGray; return }
    if (-not $Unattended) {
        $a = Read-Host "  Run this step? [Y]es / [S]kip / [Q]uit"
        if ($a -match "^[Qq]") { Write-Host "  Stopped at your request."; exit 0 }
        if ($a -match "^[Ss]") { Write-Host "  Skipped." -ForegroundColor DarkGray; return }
    }
    try { & $Command }
    catch {
        $script:Failed++
        Write-Host ("  Step $Number failed: " + $_.Exception.Message) -ForegroundColor Red
        Show-Prose "The rest of the fix may depend on this. Read the write-up before carrying on." "Red"
        if (-not $Unattended) {
            $c = Read-Host "  Carry on anyway? [y/N]"
            if ($c -notmatch "^[Yy]") { exit 1 }
        }
    }
}

Write-Rule
Write-Host '  The WSUS console crashes, or clients stop reporting' -ForegroundColor White
Write-Host '  Raise the pool limit, then clean up the database' -ForegroundColor Cyan
Write-Host ''
Write-Host '  Risk: medium   Reversible   2-6 hours including cleanup'
Write-Host ''
Show-Prose 'No warranty. Use at your own risk - JbTecWiz accepts no liability. Read it before you run it, and have a backup.' 'DarkYellow'
Write-Rule

if (-not $Unattended -and -not $DryRun) {
    $go = Read-Host '  Ready? [y/N]'
    if ($go -notmatch "^[Yy]") { Write-Host "  Nothing was changed."; exit 0 }
}

Invoke-Step -Number 1 -Do 'Check the current private memory limit.' -Command {
    Import-Module WebAdministration
    Get-ItemProperty 'IIS:\AppPools\WsusPool' -Name recycling.periodicRestart.privateMemory
}

Invoke-Step -Number 2 -Do 'Raise it substantially, or set it to zero for unlimited on a dedicated server.' -Why 'The default of around 1.8GB was chosen for a small deployment and is exceeded by any WSUS server that has been running for a year. The pool recycles mid-query and the console reports a connection failure that has nothing to do with the network.' -Command {
    Set-ItemProperty 'IIS:\AppPools\WsusPool' -Name recycling.periodicRestart.privateMemory -Value 0
    Set-ItemProperty 'IIS:\AppPools\WsusPool' -Name queueLength -Value 25000
    Restart-WebAppPool WsusPool
}

Invoke-Step -Number 3 -Do 'Decline superseded and expired updates, which is where the size comes from.' -Command {
    $wsus = Get-WsusServer
    $wsus.GetUpdates() | Where-Object { $_.IsSuperseded -and -not $_.IsDeclined } | ForEach-Object { $_.Decline() }
}

Invoke-Step -Number 4 -Do 'Run the cleanup wizard''s operations from the command line, one at a time -- running them all together on a neglected server can take days.' -Command {
    Invoke-WsusServerCleanup -DeclineSupersededUpdates
    Invoke-WsusServerCleanup -DeclineExpiredUpdates
    Invoke-WsusServerCleanup -CleanupObsoleteUpdates
    Invoke-WsusServerCleanup -CleanupUnneededContentFiles
}

Invoke-Step -Number 5 -Do 'Reindex the database afterwards. This makes more difference than anything else and is not done automatically.' -Why 'The WSUS database has no maintenance plan by default. On the internal Windows Database, indexes are never rebuilt, and query times grow until the console times out -- reindexing routinely takes a query from minutes back to seconds.' -Command {
    sqlcmd -S \\.\pipe\MICROSOFT##WID\tsql\query -i C:\WsusDBMaintenance.sql
}

Invoke-Step -Number 6 -Do 'Schedule the cleanup and reindex monthly so it never gets to this state again.' -Manual

Write-Rule "  Confirm it worked"
Show-Prose 'The console opens and stays connected while browsing updates.' 'White'
Write-Host ''
if (-not $DryRun) {
    Get-WsusServer | Format-List Name,Version,ServerProtocolVersion
    (Get-WsusServer).GetUpdateCount()
}

Write-Rule
if ($script:Failed -gt 0) {
    Write-Host ("  Finished with " + $script:Failed + " failed step(s).") -ForegroundColor Yellow
    Show-Prose 'Read the full write-up at https://jbtecwiz.com/support/srv-app-wsus-console' 'Yellow'
} else {
    Write-Host '  Finished.' -ForegroundColor Green
}
Write-Host ''
Show-Prose 'To undo: Restore the previous privateMemory value. Declined updates can be approved again if needed.' 'DarkGray'
Write-Rule