Capacity Tight-Volumes Card

Status: Reviewed (spike v1)
Path: Optional spike #4 (after approved 1→2→3→7)
Stack: PowerShell (host volumes) → SQL file sizes + history/procs → Blazor Server
Builds on: SQL-06 Capacity & growth; requires dbo.SqlInstance from SQL-SPIKE-01
Goal: Show tight host volumes and top-growing DBs so disk-full is visible before jobs/backups fail.

In scope (MVP)

• PS: free/total bytes per volume/mount on each inventoried host
• T-SQL against each instance: data/log file sizes from sys.master_files
• Warehouse history samples + simple MB/day growth for DBs
• Blazor: Tight volumes card + Top growers grid
• Thresholds: Yellow < 20% free, Red < 10% free (Rick-tunable constants)
• Reuse CollectRun (CollectorName = N'Capacity')

Out of scope (later)

• Autogrowth event mining / VLF deep dive
• Auto-expand disks / shrink from UI
• Wire into spike #7 alerts (can add VolumeTight rule later in one line)
• Fancy forecast charts (days-to-full estimate is enough if cheap)

A. Database objects

CREATE TABLE dbo.HostVolume (
  HostVolumeId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  HostName NVARCHAR(128) NOT NULL,
  VolumeKey NVARCHAR(256) NOT NULL,      -- e.g. C:\ or mount path
  TotalBytes BIGINT NOT NULL,
  FreeBytes BIGINT NOT NULL,
  PctFree AS (CASE WHEN TotalBytes = 0 THEN 0 ELSE CAST(100.0 * FreeBytes / TotalBytes AS DECIMAL(5,2)) END) PERSISTED,
  CollectedAt DATETIME2(0) NOT NULL,
  CONSTRAINT UQ_HostVolume UNIQUE (HostName, VolumeKey)
);

CREATE TABLE dbo.HostVolumeHistory (
  HostVolumeHistoryId BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  HostName NVARCHAR(128) NOT NULL,
  VolumeKey NVARCHAR(256) NOT NULL,
  TotalBytes BIGINT NOT NULL,
  FreeBytes BIGINT NOT NULL,
  CollectedAt DATETIME2(0) NOT NULL
);

CREATE TABLE dbo.SqlDataFile (
  SqlDataFileId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  SqlInstanceId INT NOT NULL REFERENCES dbo.SqlInstance(SqlInstanceId),
  DatabaseName NVARCHAR(128) NOT NULL,
  FileId INT NOT NULL,
  TypeDesc NVARCHAR(60) NOT NULL,        -- ROWS/LOG
  PhysicalName NVARCHAR(512) NOT NULL,
  SizeMb DECIMAL(18,2) NOT NULL,
  GrowthMb DECIMAL(18,2) NULL,
  IsPercentGrowth BIT NULL,
  CollectedAt DATETIME2(0) NOT NULL,
  CONSTRAINT UQ_SqlDataFile UNIQUE (SqlInstanceId, DatabaseName, FileId)
);

CREATE TABLE dbo.SqlDatabaseSizeDaily (
  SqlInstanceId INT NOT NULL,
  DatabaseName NVARCHAR(128) NOT NULL,
  SizeDate DATE NOT NULL,
  DataMb DECIMAL(18,2) NOT NULL,
  LogMb DECIMAL(18,2) NOT NULL,
  CONSTRAINT PK_SqlDatabaseSizeDaily PRIMARY KEY (SqlInstanceId, DatabaseName, SizeDate)
);

Procs:

CREATE PROC dbo.usp_Capacity_ApplyVolumeSnapshot
  @HostName NVARCHAR(128), @VolumeKey NVARCHAR(256),
  @TotalBytes BIGINT, @FreeBytes BIGINT AS ...
  -- upsert HostVolume; insert HostVolumeHistory

CREATE PROC dbo.usp_Capacity_ApplyFileSnapshot
  @SqlInstanceId INT, @DatabaseName NVARCHAR(128), @FileId INT,
  @TypeDesc NVARCHAR(60), @PhysicalName NVARCHAR(512),
  @SizeMb DECIMAL(18,2), @GrowthMb DECIMAL(18,2) = NULL, @IsPercentGrowth BIT = NULL AS ...
  -- upsert SqlDataFile; roll into SqlDatabaseSizeDaily for UTC today

CREATE PROC dbo.usp_Capacity_GetTightVolumes
  @YellowPct DECIMAL(5,2) = 20, @RedPct DECIMAL(5,2) = 10 AS
BEGIN
  SELECT HostName, VolumeKey, TotalBytes, FreeBytes, PctFree, CollectedAt,
         CASE WHEN PctFree < @RedPct THEN 'R' WHEN PctFree < @YellowPct THEN 'Y' ELSE 'G' END AS RagStatus
  FROM dbo.HostVolume
  WHERE PctFree < @YellowPct
  ORDER BY PctFree ASC;
END;

CREATE PROC dbo.usp_Capacity_GetTopGrowing
  @TopN INT = 20, @LookbackDays INT = 7 AS
BEGIN
  -- Compare latest SizeDate vs SizeDate ~ LookbackDays ago; MB/day = (DataMb+LogMb diff) / days
  SELECT TOP (@TopN) i.HostName, i.InstanceName, d.DatabaseName,
         cur.DataMb + cur.LogMb AS SizeMbNow,
         CAST(((cur.DataMb + cur.LogMb) - (old.DataMb + old.LogMb)) / NULLIF(@LookbackDays,0) AS DECIMAL(18,2)) AS MbPerDay
  FROM dbo.SqlDatabaseSizeDaily cur
  JOIN dbo.SqlInstance i ON i.SqlInstanceId = cur.SqlInstanceId
  JOIN dbo.SqlDatabaseSizeDaily old
    ON old.SqlInstanceId = cur.SqlInstanceId AND old.DatabaseName = cur.DatabaseName
   AND old.SizeDate = DATEADD(DAY, -@LookbackDays, cur.SizeDate)
  WHERE cur.SizeDate = CAST(SYSUTCDATETIME() AS DATE)
  ORDER BY MbPerDay DESC;
END;

Optional later for #7: emit findings where RagStatus = R as VolumeTight.

B. PowerShell collector (shape)

Inputs: warehouse connection; distinct HostName from active SqlInstance; per-instance connections for files.

Per host:

Get-Volume | Where-Object { $_.DriveType -eq 'Fixed' -or $_.Path } 
# map DriveLetter / PathName → VolumeKey, Size, SizeRemaining
# call usp_Capacity_ApplyVolumeSnapshot

(CIM Win32_Volume fallback if Get-Volume unavailable.)

Per instance:

SELECT DB_NAME(database_id) AS DatabaseName, file_id AS FileId, type_desc AS TypeDesc,
       physical_name AS PhysicalName,
       CAST(size AS DECIMAL(18,2)) * 8.0 / 1024 AS SizeMb,
       CASE WHEN is_percent_growth = 1 THEN growth ELSE CAST(growth AS DECIMAL(18,2)) * 8.0 / 1024 END AS GrowthMb,
       is_percent_growth AS IsPercentGrowth
FROM sys.master_files;

Then usp_Capacity_ApplyFileSnapshot each row.

Script: Collect-SqlCapacity.ps1
Rights: PS local volume read on hosts; SQL VIEW any definition / public on master_files; warehouse execute apply procs.

C. Blazor Server (MVP UI)

Page: /capacity or /sql/capacity

Cards/grids:

1. Tight volumes — Host | Volume | % Free | Free GB | Total GB | RAG
2. Top growers — Host | Instance | Database | Size MB | MB/day (7d)

Filters: Red only on volumes; search host

Actions v1: link to SQL-06 runbook only

App pool: read procs only

D. Acceptance checks

• [ ] Volume snapshot for ≥1 host with known free %
• [ ] File snapshot creates/updates SqlDataFile + today’s daily row
• [ ] After 2 days of samples (or backdated test rows), Top growers returns MB/day
• [ ] Volume at 9% free → R on tight list
• [ ] Second collect upserts without duplicate volume keys
• [ ] Mount points appear (not only C:)

E. Repo layout

/sql/capacity/001_tables.sql
/sql/capacity/002_procs.sql
/ps/Capacity/Collect-SqlCapacity.ps1
/src/.../Pages/Capacity/Index.razor
/src/.../Services/CapacityService.cs

Next (wait for pick)

Optional: #5 Perf triage, #6 Security board, #8 Build out-of-date — or wire VolumeTight into SPIKE-07 rules.

SQL Dude — SQL-SPIKE-04 Capacity Tight Volumes v1