Capacity & Growth

Status: Reviewed (deep-dive v1)
Stack: PowerShell (volume/mount free space) → SQL file-size history/procs → Blazor Server UI
Depends on: SQL-01 Inventory (instances); complements SQL-02 (backup targets need disk too)
Goal: See data/log growth early, keep volumes from surprising you, and forecast before “disk full” pages you at 2am.

Why this next

Backups and jobs fail hard when disks fill. Capacity is the bridge between “healthy today” and “still healthy next quarter.”

What “good” looks like

Per database: data + log file sizes, growth settings, last autogrowth events
Per host/volume: free space %, mount points used by SQL
Trends: 7/30/90-day growth rate for big DBs
Alerts before critical (Yellow/Red thresholds)
Blazor cards for tight volumes + fastest-growing DBs
No assumption that Autogrowth forever = a plan

Starting thresholds (confirm with Rick)

Signal	Yellow	Red
Volume free space	< 20%	< 10% (or < fixed GB floor for small disks)
Log file / data ratio unusual	Log > 50% of data without reason	Log runaway / repeated growth
Days-to-full (linear forecast)	< 30 days	< 14 days
Autogrowth storms	Many growth events / day	Growth during peak + user impact

Tune floors for tiny system volumes vs large data LUNs.

Checklist — collect (PowerShell + T-SQL)

T-SQL (per instance):

[ ] sys.databases + sys.master_files: size, max_size, growth, is_percent_growth, type (rows/log), physical_name
[ ] Optional: recent autogrowth from default trace / fn_trace_gettable or XE if already deployed (don’t invent heavy tracing in v1)
[ ] VLF count for large logs (high VLF = smell)
[ ] Emit per-file rows + CollectedAt

PowerShell (per host):

[ ] Volume / mount point free + total bytes (CIM/Get-Volume / Win32_Volume)
[ ] Map SQL physical_name roots → volume (best-effort)
[ ] Optional: disk latency counters (queue length) as secondary signal — keep light

Read-only. Don’t shrink in collectors.

Checklist — store (SQL + stored procedures)

Table	Purpose
`dbo.SqlDataFile`	Latest file inventory per DB
`dbo.SqlDataFileHistory`	Size samples over time
`dbo.HostVolume`	Volume free/total per host
`dbo.HostVolumeHistory`	Volume samples
`dbo.CapacityForecast`	Optional cached days-to-full

Procs:

[ ] usp_Capacity_ApplyFileSnapshot — upsert files + history
[ ] usp_Capacity_ApplyVolumeSnapshot — upsert volumes + history
[ ] usp_Capacity_GetTightVolumes — Blazor volume card
[ ] usp_Capacity_GetTopGrowing — fastest growers (MB/day)
[ ] usp_Capacity_GetDatabaseDetail — file layout + sparkline data

Forecast (simple v1): linear regression or (latest − size_30d_ago) / 30 → days until volume free < Red floor. Label as estimate.

Checklist — present (Blazor Server)

[ ] Tight volumes list (host, path, % free, days-to-full)
[ ] Top growing databases (MB/day, current size)
[ ] DB detail: files, growth settings, % of volume
[ ] Filters: Red only, instance, drive letter / mount
[ ] Actions v1: link runbook; flag “needs file growth plan” — no shrink/auto-fix from UI

Operator runbook (short)

Open Tight volumes → Red first
Identify which SQL files live on that volume (detail blade)
Check growth settings: tiny percent growth on huge files = fragmentation storms; huge fixed growth = long stalls
Free space options (change control): add disk, move data file, archive/purge, fix runaway log (open transactions, AG redo, missing log backups — see SQL-02)
Avoid routine SHRINKFILE as a lifestyle — use for one-time reclaim after cleanup, then set sane growth
After change: confirm next collect shows improved free % / slowed growth

Common failure patterns

Symptom	Likely cause
Log full	Missing log backup; long tran; replication/AG lag
Data autogrowth during peak	Poor initial size; percent growth; unexpected load
Volume Red but DBs small	Non-SQL consumers; leftover backups on data disk
Forecast flaps	Sparse history; restore inflated size; one-time load
Mount point missed	PS only checked drive letters

Tie-ins to other topics

SQL-02: backup destination disks need the same volume watch
SQL-03: index rebuilds need free space headroom (often ~1.2× largest index)
SQL-04: jobs fail with “disk full” / “log full” — link findings

Permissions notes

PS: local volume query rights on SQL hosts
SQL collect: view server state / read catalog
Blazor: read procs only
Disk expansion / file moves: infra + DBA change control

Done definition for this topic

[ ] File + volume snapshots for inventoried instances/hosts
[ ] Blazor shows tight volumes and top growers
[ ] At least one forecast or threshold path validated
[ ] This note linked from the Knowledge Base list item

Next candidates (when Rick directs)

Performance health · Patching & build currency · HA/DR awareness · Alerting & runbooks

SQL Dude — SQL-06 Capacity & Growth v1