Disk Space Alerts for SQL Server

SQL Server Auditing: Disk Space Alerts for SQL Server

Applies to SQL Server 2019, 2022 and 2025 on Windows. The script runs in Windows PowerShell 5.1 or PowerShell 7.

A full disk is one of the few things that stops SQL Server dead. The log can’t grow, so every write in that database fails, and if it’s tempdb the whole instance goes with it. Disk space alerts are cheap to set up and they turn a 2am outage into a ticket you deal with after coffee.

My report records free space on every volume, every hour, and warns on two things: how much space is left, and how soon it will run out at the current rate.

What the report shows

  • Every fixed volume on every server, mount points included, with size, free GB and free percentage.
  • Status. CRITICAL below 10% or 10 GB free, WARNING below 20% free or when it’s due to fill within 30 days, otherwise OK.
  • Growth. GB used per day over the last week.
  • Days until full at that rate. It’s blank when the volume isn’t filling up.

Why it matters

A percentage on its own is a poor alarm. 10% free on a 4 TB data drive is 400 GB, which may be months of growth. 10% free on a 50 GB system drive is 5 GB, which one Windows update can eat. That’s why the critical rule also looks at free GB.

The growth figure is what makes the report useful in a capacity meeting. “The data drive on the finance server fills in 23 days” gets disk ordered. “It’s at 18%” gets a shrug.

It also catches the sudden ones. A log drive that used 2 GB a day all month and 60 GB yesterday has a runaway transaction or a log backup that stopped. You find that out from the days-until-full number dropping, not from the application team.

The script

It uses Win32_Volume rather than Win32_LogicalDisk, because SQL Server estates often use mount points, and Win32_LogicalDisk only sees drive letters. Volumes with no drive letter or mount point, such as recovery partitions, are skipped.

# Invoke-DiskInventory.ps1
# Records size and free space for every fixed volume on every server,
# mount points included. Schedule it hourly (daily is the bare minimum).

# ---- Settings ---------------------------------------------------------------
$ServerListPath    = 'C:\DBA\Servers.txt'
$InventoryServer   = 'SQLMON01'
$InventoryDatabase = 'DBA'
# -----------------------------------------------------------------------------

. "$PSScriptRoot\DbaReportHelpers.ps1"

$Servers = Get-ServerList -Path $ServerListPath
$Repo    = Open-SqlConnection -Server $InventoryServer -Database $InventoryDatabase

try {
    Invoke-SqlNonQuery $Repo @"
IF OBJECT_ID('dbo.ServerDiskInventory', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.ServerDiskInventory
    (
        CaptureDate DATETIME2(0)  NOT NULL
                    CONSTRAINT DF_ServerDiskInventory_CaptureDate DEFAULT SYSDATETIME(),
        ServerName  NVARCHAR(128) NOT NULL,
        Drive       NVARCHAR(260) NOT NULL,
        VolumeLabel NVARCHAR(128) NULL,
        SizeGB      DECIMAL(18,2) NOT NULL,
        FreeGB      DECIMAL(18,2) NOT NULL,
        FreePct     DECIMAL(5,2)  NOT NULL
    );

    CREATE CLUSTERED INDEX CX_ServerDiskInventory
        ON dbo.ServerDiskInventory (ServerName, Drive, CaptureDate);
END;
"@

    foreach ($Server in $Servers) {
        try {
            # DriveType 3 = local fixed disk. Win32_Volume also returns mount points,
            # which Win32_LogicalDisk misses. Volumes with no drive letter or mount
            # point (recovery partitions and so on) have a \\?\ name: skip them.
            $Volumes = @(Get-CimInstance -ClassName Win32_Volume -ComputerName (Get-ComputerName $Server) `
                            -Filter 'DriveType = 3' -OperationTimeoutSec 60 -ErrorAction Stop |
                         Where-Object { $_.Capacity -gt 0 -and $_.Name -notlike '\\?\*' })

            foreach ($Volume in $Volumes) {
                Add-InventoryRow -Connection $Repo -Table 'dbo.ServerDiskInventory' -Row ([ordered]@{
                    ServerName  = $Server
                    Drive       = $Volume.Name
                    VolumeLabel = $Volume.Label
                    SizeGB      = [math]::Round($Volume.Capacity  / 1GB, 2)
                    FreeGB      = [math]::Round($Volume.FreeSpace / 1GB, 2)
                    FreePct     = [math]::Round($Volume.FreeSpace * 100 / $Volume.Capacity, 2)
                })
            }

            Write-Host "$Server : $($Volumes.Count) volumes"
        }
        catch {
            Write-Warning "$Server : $($_.Exception.Message)"
        }
    }
}
finally {
    $Repo.Close()
}
DbaReportHelpers.ps1: the shared helper file (save it in the same folder as the script)
# DbaReportHelpers.ps1
# Shared helpers for the auditing scripts. Save it next to them and dot-source it:
#     . "$PSScriptRoot\DbaReportHelpers.ps1"
# Windows Authentication throughout: run the scripts as a service account that
# can reach every server. No passwords live in any of these files.

function Get-ServerList {
    # One server per line. Blank lines and anything after # are ignored,
    # and duplicates (in any case) are removed.
    param([Parameter(Mandatory)][string]$Path)

    Get-Content -Path $Path |
        ForEach-Object { ($_ -split '#')[0].Trim() } |
        Where-Object { $_ } |
        Sort-Object -Unique
}

function Get-ComputerName {
    # SERVER\INSTANCE -> SERVER, for the Windows calls (CIM, ping, remoting)
    param([Parameter(Mandatory)][string]$Server)
    ($Server -split '\\')[0]
}

function Open-SqlConnection {
    param(
        [Parameter(Mandatory)][string]$Server,
        [string]$Database = 'master'
    )

    $Conn = New-Object System.Data.SqlClient.SqlConnection
    $Conn.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True;" +
                             "TrustServerCertificate=True;Connect Timeout=15;Application Name=DBA reports"
    $Conn.Open()
    $Conn
}

function Invoke-SqlQuery {
    # Runs a query and returns a DataTable (the leading comma stops PowerShell unrolling it).
    param($Connection, [string]$Sql, [hashtable]$Parameters = @{})

    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = $Sql
    $Cmd.CommandTimeout = 300
    foreach ($Name in $Parameters.Keys) {
        $null = $Cmd.Parameters.AddWithValue("@$Name", $Parameters[$Name])
    }
    $Table = New-Object System.Data.DataTable
    $null = (New-Object System.Data.SqlClient.SqlDataAdapter $Cmd).Fill($Table)
    , $Table
}

function Invoke-SqlNonQuery {
    param($Connection, [string]$Sql)

    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = $Sql
    $Cmd.CommandTimeout = 300
    $null = $Cmd.ExecuteNonQuery()
}

function Add-InventoryRow {
    # Inserts one row from an ordered hashtable of column = value.
    # Every value goes in as a parameter, never pasted into the SQL text.
    param(
        $Connection,
        [Parameter(Mandatory)][string]$Table,
        [Parameter(Mandatory)][System.Collections.IDictionary]$Row
    )

    $Columns = @($Row.Keys)
    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = "INSERT INTO $Table (" + ($Columns -join ', ') + ') VALUES (' +
                       (($Columns | ForEach-Object { "@$_" }) -join ', ') + ');'

    foreach ($Column in $Columns) {
        $Value = $Row[$Column]
        if ($null -eq $Value -or $Value -is [System.DBNull] -or
            ($Value -is [string] -and $Value.Trim() -eq '')) {
            $Value = [System.DBNull]::Value
        }
        $null = $Cmd.Parameters.AddWithValue("@$Column", $Value)
    }
    $null = $Cmd.ExecuteNonQuery()
}

The report and the alert

The view compares the newest reading for each volume with the oldest in the last seven days to get the daily rate. The SQL Agent job step at the end e-mails only when something isn’t OK, so a quiet inbox means a healthy estate.

-- Run once in the DBA database. The view is the report; the job step below is the alert.
CREATE OR ALTER VIEW dbo.vDiskSpaceStatus
AS
WITH Ranked AS
(
    SELECT ServerName, Drive, VolumeLabel, SizeGB, FreeGB, FreePct, CaptureDate,
           ROW_NUMBER() OVER (PARTITION BY ServerName, Drive ORDER BY CaptureDate DESC) AS Newest,
           ROW_NUMBER() OVER (PARTITION BY ServerName, Drive ORDER BY CaptureDate ASC)  AS Oldest
    FROM dbo.ServerDiskInventory
    WHERE CaptureDate >= DATEADD(DAY, -7, SYSDATETIME())
)
SELECT
    N.ServerName,
    N.Drive,
    N.VolumeLabel,
    N.SizeGB,
    N.FreeGB,
    N.FreePct,
    CASE WHEN N.FreePct < 10 OR N.FreeGB < 10 THEN 'CRITICAL'
         WHEN N.FreePct < 20 OR G.DaysUntilFull < 30 THEN 'WARNING'
         ELSE 'OK' END AS Status,
    G.UsedGBPerDay,
    G.DaysUntilFull,
    N.CaptureDate AS LastChecked
FROM Ranked AS N
LEFT JOIN Ranked AS O
       ON O.ServerName = N.ServerName AND O.Drive = N.Drive AND O.Oldest = 1
CROSS APPLY
(
    SELECT
        Rate = (O.FreeGB - N.FreeGB)
               / NULLIF(DATEDIFF(MINUTE, O.CaptureDate, N.CaptureDate) / 1440.0, 0)
) AS R
CROSS APPLY
(
    SELECT
        UsedGBPerDay  = CAST(R.Rate AS DECIMAL(18,2)),
        DaysUntilFull = CAST(CASE WHEN R.Rate > 0 THEN N.FreeGB / R.Rate END AS DECIMAL(18,0))
) AS G
WHERE N.Newest = 1;
GO

-- The report: worst first
SELECT *
FROM dbo.vDiskSpaceStatus
ORDER BY CASE Status WHEN 'CRITICAL' THEN 0 WHEN 'WARNING' THEN 1 ELSE 2 END, FreePct;

-- Agent job step on the DBA server, every hour: e-mail only when something is wrong
IF EXISTS (SELECT 1 FROM DBA.dbo.vDiskSpaceStatus WHERE Status <> 'OK')
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = N'DBA Mail',
        @recipients   = N'[email protected]',
        @subject      = N'Disk space warning',
        @query        = N'SET NOCOUNT ON;
SELECT ServerName, Drive, FreeGB, FreePct, DaysUntilFull, Status
FROM DBA.dbo.vDiskSpaceStatus
WHERE Status <> ''OK''
ORDER BY CASE Status WHEN ''CRITICAL'' THEN 0 ELSE 1 END, FreePct;',
        @query_result_width = 200;
END;

Change the Database Mail profile name and recipients to your own. A distribution list or a ticketing system address is better than a person.

Scheduling it

Run the PowerShell collector hourly from Task Scheduler, and the alert job a few minutes after it. Daily collection still works for the capacity figures, but an hourly run is what catches a log drive filling up the same afternoon.

Watch out for

  • Thin provisioning. Windows can show plenty of free space on a volume whose storage pool or datastore is nearly full. Ask the storage team for their numbers as well. This report can’t see below the OS.
  • Autogrowth settings. A database set to grow by 10% on a 500 GB file grows 50 GB at a time. The days-until-full figure won’t predict that jump. Fixed growth sizes in MB make the trend honest.
  • Shrinking to make the alert go away. If a data file shrinks today, it grows again tomorrow and fragments everything on the way. Fix the cause or add disk.
  • WinRM. Get-CimInstance -ComputerName uses WinRM. If a server only allows DCOM, create a session with New-CimSessionOption -Protocol Dcom and pass it with -CimSession.
  • Retired drives. A volume that’s removed stops reporting, so it drops off the report after seven days. A volume that suddenly disappears is worth a question in its own right.

Where it fits

This is part of my Auditing section, with the licensing, availability, inventory, database, backup and SSIS reports. The database report shows how much of each file is actually used, which is the other half of the capacity picture. More on file layout is in the SQL Server DBA Handbook.

The other Auditing reports: Licensing | Availability: ping and response time | Server inventory | Database inventory | Backups | SSIS errors

Clive Roberts DBA