Public/Get-sqmDiskSpaceReport.ps1
|
<#
.SYNOPSIS Determines free disk space on all SQL-relevant volumes and estimates time to exhaustion based on growth data. .DESCRIPTION Queries sys.dm_os_volume_stats for all database files and determines: - Free disk space per volume - Total size of database files on the volume - AutoGrowth volume over the last -HistoryDays days (from default trace) - Estimated days until exhaustion based on growth rate - Warning when free space falls below -WarnThresholdPct Results are saved as TXT report and CSV file in the specified directory. The function also returns an object with the detail data and file paths. .PARAMETER SqlInstance SQL Server instance(s). Pipeline-capable. Default: current computer name. .PARAMETER SqlCredential Optional PSCredential for the connection. .PARAMETER WarnThresholdPct Warning when free space falls below this percentage. Default: 20. .PARAMETER CriticalThresholdPct Critical when free space falls below this percentage. Default: 10. .PARAMETER HistoryDays Time range for growth calculation in days. Default: 30. .PARAMETER OutputPath Output directory for report files. Default: C:\System\WinSrvLog\MSSQL .PARAMETER ContinueOnError Continue on error for an instance (otherwise the error is thrown). .PARAMETER EnableException Throw exceptions immediately (overrides ContinueOnError). .PARAMETER Confirm Request confirmation before writing files. .PARAMETER WhatIf Shows which files would be created without actually writing them. .EXAMPLE Get-sqmDiskSpaceReport .EXAMPLE Get-sqmDiskSpaceReport -SqlInstance "SQL01" -WarnThresholdPct 15 -OutputPath "D:\Reports" .NOTES Author: MSSQLTools Prerequisites: dbatools, Invoke-sqmLogging Default output path: C:\System\WinSrvLog\MSSQL Growth calculation is based on the default trace (if enabled). If the trace is disabled, no growth value is determined. #> function Get-sqmDiskSpaceReport { [CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'None')] [OutputType([PSCustomObject])] param ( [Parameter(Mandatory = $false, ValueFromPipeline = $true)] [string[]]$SqlInstance = @($env:COMPUTERNAME), [Parameter(Mandatory = $false)] [System.Management.Automation.PSCredential]$SqlCredential, [Parameter(Mandatory = $false)] [ValidateRange(1, 99)] [int]$WarnThresholdPct = 20, [Parameter(Mandatory = $false)] [ValidateRange(1, 99)] [int]$CriticalThresholdPct = 10, [Parameter(Mandatory = $false)] [int]$HistoryDays = 30, [Parameter(Mandatory = $false)] [string]$OutputPath = "C:\System\WinSrvLog\MSSQL", [Parameter(Mandatory = $false)] [switch]$ContinueOnError, [Parameter(Mandatory = $false)] [switch]$EnableException, [Parameter(Mandatory = $false)] [switch]$NoOpen ) begin { $functionName = $MyInvocation.MyCommand.Name $allInstanceResults = [System.Collections.Generic.List[PSCustomObject]]::new() if (-not (Get-Module -ListAvailable -Name dbatools)) { $errMsg = "dbatools-Modul nicht gefunden." Invoke-sqmLogging -Message $errMsg -FunctionName $functionName -Level "ERROR" throw $errMsg } Invoke-sqmLogging -Message "Starte $functionName mit OutputPath: $OutputPath" -FunctionName $functionName -Level "INFO" } process { foreach ($instance in $SqlInstance) { $connParams = @{ SqlInstance = $instance } if ($SqlCredential) { $connParams['SqlCredential'] = $SqlCredential } $detailRows = [System.Collections.Generic.List[PSCustomObject]]::new() try { Invoke-sqmLogging -Message "[$instance] Lade Volume-Statistiken ..." -FunctionName $functionName -Level "INFO" # 1. Volumedaten aus sys.dm_os_volume_stats $volumeQuery = @" SELECT DISTINCT vs.volume_mount_point AS MountPoint, vs.logical_volume_name AS VolumeName, vs.total_bytes / 1073741824.0 AS TotalGB, vs.available_bytes / 1073741824.0 AS FreeGB, (vs.total_bytes - vs.available_bytes) / 1073741824.0 AS UsedGB, CAST(vs.available_bytes * 100.0 / NULLIF(vs.total_bytes,0) AS DECIMAL(5,1)) AS FreePct FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs ORDER BY vs.volume_mount_point; "@ # Verbindungs-/Auth-Fehler explizit fangen (EnableException erzwingen), # damit der echte Fehler nicht als "keine Daten" maskiert wird. $volRows = $null try { $volRows = Invoke-DbaQuery @connParams -Query $volumeQuery -EnableException:$true } catch { $origMsg = $_.Exception.Message # Kerberos / SPN / SSPI-Hinweis ergaenzen $hint = '' if ($origMsg -match 'target principal name|Kerberos|SSPI|cannot generate sspi') { $hint = ' | Kerberos/SPN-Problem: FQDN statt Alias verwenden, SPN pruefen (setspn -L <Dienstkonto>), oder -SqlCredential nutzen.' } $errMsg = "Verbindung/Abfrage fehlgeschlagen: $origMsg$hint" Invoke-sqmLogging -Message "[$instance] $errMsg" -FunctionName $functionName -Level "ERROR" $allInstanceResults.Add([PSCustomObject]@{ SqlInstance = $instance Status = 'Error' Message = $errMsg DetailRows = @() TxtFile = $null CsvFile = $null }) if ($EnableException) { throw } continue } # Verbunden, aber keine Zeilen (auf einer echten Instanz praktisch nie der Fall, # da sys.master_files immer Systemdatenbanken enthaelt). if (-not $volRows) { Invoke-sqmLogging -Message "[$instance] Verbindung ok, aber keine Volumedaten zurueckgegeben." -FunctionName $functionName -Level "WARNING" $allInstanceResults.Add([PSCustomObject]@{ SqlInstance = $instance Status = 'Warning' Message = 'Keine Volumedaten zurueckgegeben (Verbindung war erfolgreich).' DetailRows = @() TxtFile = $null CsvFile = $null }) continue } # 2. AutoGrowth-Wachstum aus Default Trace (optional) $growthLookup = @{ } try { $growthQuery = @" DECLARE @tracefile NVARCHAR(500); SELECT @tracefile = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),500)) + N'log.trc' FROM sys.traces WHERE is_default = 1; IF @tracefile IS NOT NULL BEGIN SELECT LEFT(FileName, LEN(FileName) - CHARINDEX('\', REVERSE(FileName))) AS FolderPath, SUM(CAST(IntegerData AS BIGINT) * 8 * 1024) AS TotalGrowthBytes FROM sys.fn_trace_gettable(@tracefile, DEFAULT) WHERE EventClass IN (92,93) AND StartTime >= DATEADD(DAY, -$HistoryDays, GETDATE()) GROUP BY LEFT(FileName, LEN(FileName) - CHARINDEX('\', REVERSE(FileName))); END "@ $growthRows = Invoke-DbaQuery @connParams -Query $growthQuery -EnableException:$false -ErrorAction SilentlyContinue foreach ($r in $growthRows) { $drive = if ($r.FolderPath -match '^([A-Za-z]:)') { $Matches[1].ToUpper() + '\' } else { $r.FolderPath } $growthLookup[$drive] = [math]::Round($r.TotalGrowthBytes / 1073741824.0, 2) } if ($growthRows.Count -eq 0) { Invoke-sqmLogging -Message "[$instance] Keine AutoGrowth-Daten im Default Trace gefunden (Trace evtl. deaktiviert)." -FunctionName $functionName -Level "VERBOSE" } } catch { Invoke-sqmLogging -Message "[$instance] Fehler beim Lesen des Default Trace: $($_.Exception.Message)" -FunctionName $functionName -Level "WARNING" } # 3. Detailzeilen aufbereiten foreach ($vol in $volRows) { $mount = $vol.MountPoint $totalGB = [math]::Round($vol.TotalGB, 1) $freeGB = [math]::Round($vol.FreeGB, 1) $usedGB = [math]::Round($vol.UsedGB, 1) $freePct = [math]::Round($vol.FreePct, 1) $growthGB = $growthLookup[$mount] $growthPerDay = if ($growthGB -and $HistoryDays -gt 0) { [math]::Round($growthGB / $HistoryDays, 3) } else { 0 } $daysUntilFull = if ($growthPerDay -gt 0) { [math]::Round($freeGB / $growthPerDay, 0) } else { $null } $status = if ($freePct -le $CriticalThresholdPct) { 'Critical' } elseif ($freePct -le $WarnThresholdPct) { 'Warning' } elseif ($daysUntilFull -and $daysUntilFull -le 30) { 'Warning' } else { 'OK' } $detailRows.Add([PSCustomObject]@{ SqlInstance = $instance MountPoint = $mount VolumeName = $vol.VolumeName TotalGB = $totalGB UsedGB = $usedGB FreeGB = $freeGB FreePct = $freePct GrowthLastPeriodGB = if ($growthGB) { $growthGB } else { $null } GrowthPerDayGB = if ($growthPerDay -gt 0) { $growthPerDay } else { $null } DaysUntilFull = $daysUntilFull HistoryDays = $HistoryDays Status = $status Message = switch ($status) { 'Critical' { "Kritisch: nur $freePct% frei ($freeGB GB)!" } 'Warning' { if ($daysUntilFull -le 30 -and $daysUntilFull) { "Warnung: voll in ca. $daysUntilFull Tagen." } else { "Warnung: nur $freePct% frei ($freeGB GB)." } } default { "OK: $freePct% frei ($freeGB GB)." } } }) } # 4. Berichtsdateien schreiben $timestamp = Get-Date -Format 'yyyy-MM-dd HH:mm:ss' $datestamp = Get-Date -Format 'yyyy-MM-dd' $safeInst = $instance -replace '[\\/:*?"<>|]', '_' $txtFile = Join-Path $OutputPath "DiskSpaceReport_${safeInst}_${datestamp}.txt" $csvFile = Join-Path $OutputPath "DiskSpaceReport_${safeInst}_${datestamp}.csv" $htmlFile = Join-Path $OutputPath "DiskSpaceReport_${safeInst}_${datestamp}.html" $cntCrit = ($detailRows | Where-Object Status -eq 'Critical').Count $cntWarn = ($detailRows | Where-Object Status -eq 'Warning').Count if ($PSCmdlet.ShouldProcess($instance, "Erstelle Disk-Space-Bericht in $OutputPath")) { # Verzeichnis anlegen if (-not (Test-Path $OutputPath)) { New-Item -ItemType Directory -Path $OutputPath -Force -ErrorAction Stop | Out-Null Invoke-sqmLogging -Message "Verzeichnis $OutputPath wurde erstellt." -FunctionName $functionName -Level "INFO" } # TXT-Bericht $reference = Get-sqmReportReference $lines = [System.Collections.Generic.List[string]]::new() $lines.Add("# ================================================================") $lines.Add("# sqmSQLTool - Disk Space Report") $lines.Add("# $reference") $lines.Add("# Instanz : $instance") $lines.Add("# Erstellt : $timestamp") $lines.Add("# Warn: <${WarnThresholdPct}% | Critical: <${CriticalThresholdPct}% | Wachstum: letzte $HistoryDays Tage") $lines.Add("# Critical: $cntCrit | Warning: $cntWarn") $lines.Add("# ================================================================") $lines.Add("") $lines.Add(("{0,-6} {1,-20} {2,-8} {3,-8} {4,-8} {5,-7} {6,-10} {7,-8} {8}" -f 'Status', 'Laufwerk', 'TotalGB', 'UsedGB', 'FreeGB', 'Free%', 'GrowthGB', 'DaysFull', 'Info')) $lines.Add(("-" * 105)) foreach ($e in ($detailRows | Sort-Object Status, MountPoint)) { $growthDisplay = if ($e.GrowthLastPeriodGB) { $e.GrowthLastPeriodGB } else { 'n/a' } $daysDisplay = if ($e.DaysUntilFull) { $e.DaysUntilFull } else { '?' } $lines.Add(("{0,-6} {1,-20} {2,-8} {3,-8} {4,-8} {5,-7} {6,-10} {7,-8} {8}" -f $e.Status, $e.MountPoint, $e.TotalGB, $e.UsedGB, $e.FreeGB, "$($e.FreePct)%", $growthDisplay, $daysDisplay, $e.Message)) } $lines | Out-File -FilePath $txtFile -Encoding UTF8 -Force # CSV-Datei $detailRows | Export-Csv -Path $csvFile -Encoding UTF8 -NoTypeInformation -Force # HTML-Bericht (farbcodiert nach Status) $rowsHtml = '' foreach ($e in ($detailRows | Sort-Object Status, MountPoint)) { $cls = switch ($e.Status) { 'Critical' { 'crit' } 'Warning' { 'warn' } default { 'ok' } } $growthDisplay = if ($e.GrowthLastPeriodGB) { $e.GrowthLastPeriodGB } else { 'n/a' } $daysDisplay = if ($e.DaysUntilFull) { $e.DaysUntilFull } else { '?' } $mp = [string]$e.MountPoint -replace '&', '&' -replace '<', '<' -replace '>', '>' $rowsHtml += "<tr><td class='$cls'>$($e.Status)</td><td>$mp</td><td>$($e.TotalGB)</td><td>$($e.UsedGB)</td><td>$($e.FreeGB)</td><td>$($e.FreePct)%</td><td>$growthDisplay</td><td>$daysDisplay</td></tr>`n" } $bodyHtml = @" <table> <thead><tr><th>Status</th><th>Laufwerk</th><th>Total GB</th><th>Used GB</th><th>Free GB</th><th>Free %</th><th>Growth GB</th><th>Days Full</th></tr></thead> <tbody> $rowsHtml </tbody> </table> <p style="color:#94a8c0;font-size:12px;">Warn: <${WarnThresholdPct}% | Critical: <${CriticalThresholdPct}% | Wachstum: letzte $HistoryDays Tage | Critical: $cntCrit, Warning: $cntWarn</p> "@ $html = ConvertTo-sqmHtmlReport -Title "Disk Space Report - $instance" -Subtitle "Erstellt: $timestamp" -BodyHtml $bodyHtml $html | Out-File -FilePath $htmlFile -Encoding UTF8 -Force # Oeffnen: HTML vor TXT, CSV nie. -NoOpen unterdrueckt. Invoke-sqmOpenReport -HtmlFile $htmlFile -TxtFile $txtFile -NoOpen:$NoOpen Invoke-sqmLogging -Message "[$instance] Disk-Space-Bericht erstellt: $htmlFile" -FunctionName $functionName -Level "INFO" } else { Invoke-sqmLogging -Message "[$instance] WhatIf: Berichtsdateien wuerden erstellt werden." -FunctionName $functionName -Level "VERBOSE" $txtFile = $null $csvFile = $null $htmlFile = $null } # Ergebnisobjekt fuer diese Instanz $result = [PSCustomObject]@{ SqlInstance = $instance Timestamp = $timestamp DetailRows = $detailRows TxtFile = $txtFile CsvFile = $csvFile HtmlFile = $htmlFile Status = if ($cntCrit -gt 0) { 'Critical' } elseif ($cntWarn -gt 0) { 'Warning' } else { 'OK' } } $allInstanceResults.Add($result) if ($cntCrit -gt 0) { Invoke-sqmLogging -Message "[$instance] $cntCrit Critical Disk-Space-Issue(s) - Bericht: $txtFile" -FunctionName $functionName -Level "WARNING" } } catch { $errMsg = "Fehler auf '$instance': $($_.Exception.Message)" Invoke-sqmLogging -Message $errMsg -FunctionName $functionName -Level "ERROR" $allInstanceResults.Add([PSCustomObject]@{ SqlInstance = $instance Status = 'Error' Message = $errMsg DetailRows = $null TxtFile = $null CsvFile = $null }) if ($EnableException) { throw } if (-not $ContinueOnError) { throw $_ } } } } end { Invoke-sqmLogging -Message "$functionName abgeschlossen. $($allInstanceResults.Count) Instanzen verarbeitet." -FunctionName $functionName -Level "INFO" return $allInstanceResults } } |