sqmSQLTool › Commands › Get-sqmDiskSpaceReport

Get-sqmDiskSpaceReport Configuration

Reports per-volume disk space for all SQL Server data/log volumes, with linear-regression growth forecast (Method B1) from JSON snapshot history and optional backup-history bootstrap (Method B2). Outputs color-coded TXT, CSV, and HTML reports. Warns on low free-space and projected fill dates within 30 days.

Execution Flow

START dbatools available? throw ERROR Invoke-DbaQuery: sys.dm_os_volume_stats CROSS APPLY sys.master_files → MountPoint, TotalGB, FreeGB, FreePct per volume (deduped) Get-sqmVolumeForecast (private) — Method B1: snapshot history Load JSON: OutputPath\History\DiskHistory_<safeInst>.json Append today's snapshot (unless -NoHistory); atomic write via .tmp + Move-Item Retention: 400 days; linear least-squares regression on UsedGB/day DaysUntilFull = FreeGB / slope; Confidence: High (n≥7,R²≥0.8) / Medium / Low if -SeedFromBackupHistory → Method B2: Get-sqmBackupGrowthSeed (ForecastBasis=BackupHistory) msdb.dbo.backupset (type=D) → DB growth → map to volume via master_files path prefix foreach $vol — apply thresholds Critical: FreePct ≤ -CriticalThresholdPct (10%) or DaysUntilFull ≤ 30 Warning: FreePct ≤ -WarnThresholdPct (20%) or DaysUntilFull ≤ 90 ShouldProcess → TXT + CSV + HTML (color-coded .crit/.warn/.ok) Invoke-sqmOpenReport -HtmlFile Return $allVolumes[] DONE

Synopsis

Uses sys.dm_os_volume_stats CROSS APPLY sys.master_files to enumerate every volume hosting a SQL data or log file. A JSON snapshot file accumulates daily readings; a linear least-squares regression over these points yields a per-volume growth rate (GrowthPerDayGB) and estimated days until full. When fewer than MinDataPoints (5) snapshots exist, the backup history bootstrap (Method B2) can seed the regression from msdb.dbo.backupset.

Requires dbatools. Snapshot history is appended atomically using a .tmp file and Move-Item. History older than 400 days is pruned automatically. The HTML report color-codes rows with CSS classes .crit, .warn, and .ok.

Forecast Confidence Levels

ConfidenceCriteria
High≥ 7 data points AND R² ≥ 0.80
Medium≥ 5 data points AND R² ≥ 0.50
LowFewer points, lower R², or bootstrapped from backup history.

Syntax

Get-sqmDiskSpaceReport
    [-SqlInstance <String>]
    [-SqlCredential <PSCredential>]
    [-OutputPath <String>]
    [-WarnThresholdPct <Int>]       # default 20
    [-CriticalThresholdPct <Int>]   # default 10
    [-SeedFromBackupHistory]         # Method B2 bootstrap
    [-NoHistory]                     # skip snapshot append
    [-EnableException]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString$env:COMPUTERNAMESQL Server instance to query.
-SqlCredentialPSCredential—SQL connection credential.
-OutputPathStringGet-sqmConfig 'OutputPath'Directory for reports and JSON history subfolder.
-WarnThresholdPctInt20Warning threshold: free space percent.
-CriticalThresholdPctInt10Critical threshold: free space percent.
-SeedFromBackupHistorySwitchfalseBootstrap forecast from msdb backup history when snapshot data is insufficient.
-NoHistorySwitchfalseRead-only mode — do not append today's snapshot to JSON history.
-EnableExceptionSwitchfalseThrow terminating exceptions.

Return Value

PropertyDescription
MountPointVolume mount point or drive letter.
TotalGB / FreeGB / FreePctCurrent disk space values.
GrowthPerDayGBEstimated daily growth in GB from regression.
DaysUntilFullProjected days until volume is full.
HistoryDays / DataPointsAge and count of snapshot history records used.
ForecastConfidenceHigh / Medium / Low.
ForecastBasisSnapshotHistory or BackupHistory.
StatusOK / Warning / Critical.

Examples

# Basic report, local instance
Get-sqmDiskSpaceReport

# Bootstrap forecast from backup history (new server, no history yet)
Get-sqmDiskSpaceReport -SqlInstance "SQL01" -SeedFromBackupHistory

# Read-only: report without updating snapshot history
Get-sqmDiskSpaceReport -NoHistory | Where-Object Status -ne "OK"