Capacity Report: When Each Drive Will Be Full

SQL Server Auditing: Capacity Report: When Each Drive Will Be Full

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

The disk space alerts tell you a drive is nearly full today. This report tells you when it will be, months ahead, which is the difference between ordering disk and an emergency change on a Friday night. It keeps a daily record of every database file and the drive it sits on, and works out the growth from that.

What the report shows

  • A forecast for every volume that holds database files: size, free space, growth per month over roughly the last 90 days, and months until full. Each is marked ORDER DISK NOW (under 3 months), PLAN DISK (under 6), WATCH (under 12) or OK.
  • Database growth: data actually used (not just allocated), growth per month, and growth over the last year in GB and percent once there’s a year of history.
  • A server summary for the quarterly pack: databases, data allocated and used, log allocated, disk total and free.
  • File settings that make growth unpredictable: percentage growth, autogrowth off, tiny growth increments on big files, files over 90% of their maximum size, and files whose next growth won’t fit on the drive.

Why it matters

Storage is usually ordered on someone else’s timescale: a purchase order, a SAN team’s change window, a cloud budget approved quarterly. If the first sign of trouble is a 10% free alert, it’s already too late for any of those.

The forecast also answers the finance question. “We’ll need another 2 TB by June” with the growth figures behind it gets approved. “We’re running low” gets a meeting.

Collect daily. Look at the volume forecast monthly, and send the full report quarterly to the IT manager and whoever holds the storage budget.

The script

For each instance, it opens every online database to read the current size and used space of each file (sys.master_files on its own shows tempdb’s start-up size, not its current size), then adds the growth settings and the volume details from sys.dm_os_volume_stats. One row per file per run.

# Invoke-CapacityAudit.ps1
# One row per database file per run: size, space used, autogrowth and max size,
# and the size and free space of the volume it sits on. Keep a year of it and
# the capacity report can say how fast each database and volume is growing,
# and when each volume fills. Schedule it daily; report on it quarterly.
#
# Tables: run sql\01_Create_Tables.sql first.
# Report: sql\CapacityReport.sql

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

. "$PSScriptRoot\DbaReportHelpers.ps1"

$CapacityQuery = @"
SET NOCOUNT ON;

-- Current size and used pages per file, read inside each database.
-- (sys.master_files shows tempdb's start-up size, not its current size.)
CREATE TABLE #Files
(
    DatabaseId INT    NOT NULL,
    FileId     INT    NOT NULL,
    SizePages  BIGINT NULL,
    UsedPages  BIGINT NULL
);

DECLARE @Name SYSNAME, @Sql NVARCHAR(MAX);

DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases
    WHERE state_desc = 'ONLINE' AND HAS_DBACCESS(name) = 1;

OPEN dbs;
FETCH NEXT FROM dbs INTO @Name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = N'USE ' + QUOTENAME(@Name) + N';
INSERT INTO #Files (DatabaseId, FileId, SizePages, UsedPages)
SELECT DB_ID(), file_id, size, FILEPROPERTY(name, ''SpaceUsed'')
FROM sys.database_files;';

    BEGIN TRY
        EXEC sys.sp_executesql @Sql;
    END TRY
    BEGIN CATCH
        PRINT 'Skipped ' + @Name + ': ' + ERROR_MESSAGE();
    END CATCH;

    FETCH NEXT FROM dbs INTO @Name;
END;

CLOSE dbs;
DEALLOCATE dbs;

SELECT
    d.name                                                              AS DatabaseName,
    mf.name                                                             AS LogicalName,
    mf.type_desc                                                        AS FileType,
    mf.physical_name                                                    AS PhysicalName,
    vs.volume_mount_point                                               AS VolumeMountPoint,
    CAST(COALESCE(f.SizePages, mf.size) * 8 / 1024.0 AS DECIMAL(18,2))  AS SizeMB,
    CAST(f.UsedPages * 8 / 1024.0 AS DECIMAL(18,2))                     AS UsedMB,
    CASE WHEN mf.is_percent_growth = 0
         THEN CAST(mf.growth * 8 / 1024.0 AS DECIMAL(18,2)) END         AS GrowthMB,
    CASE WHEN mf.is_percent_growth = 1 THEN mf.growth END               AS GrowthPct,
    CASE WHEN mf.max_size IN (-1, 268435456) THEN NULL                  -- unlimited (268435456 = 2 TB log)
         ELSE CAST(mf.max_size * 8 / 1024.0 AS DECIMAL(18,2)) END       AS MaxSizeMB,
    CAST(vs.total_bytes     / 1048576.0 AS DECIMAL(18,2))               AS VolumeTotalMB,
    CAST(vs.available_bytes / 1048576.0 AS DECIMAL(18,2))               AS VolumeFreeMB
FROM sys.master_files AS mf
JOIN sys.databases    AS d  ON d.database_id = mf.database_id
LEFT JOIN #Files      AS f  ON f.DatabaseId = mf.database_id AND f.FileId = mf.file_id
OUTER APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
WHERE d.state_desc = 'ONLINE';
"@

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

try {
    Assert-InventoryTable $Repo 'dbo.CollectionLog', 'dbo.DatabaseFileInventory'

    foreach ($Server in $Servers) {
        try {
            $Source = Open-SqlConnection -Server $Server
            try {
                $Data = Invoke-SqlQuery $Source $CapacityQuery
            }
            finally {
                $Source.Close()
            }

            $Files = Add-DataTableRows $Repo 'dbo.DatabaseFileInventory' $Data $Server $CaptureDate
            Write-CollectionLog $Repo -Collection 'Capacity' -ServerName $Server -CaptureDate $CaptureDate `
                                -Status 'SUCCESS' -Rows $Files
            Write-Host "$Server : $Files database files"
        }
        catch {
            Write-CollectionLog $Repo -Collection 'Capacity' -ServerName $Server -CaptureDate $CaptureDate `
                                -Status 'FAILED' -ErrorMessage $_.Exception.Message
            Write-Warning "$Server : $($_.Exception.Message)"
        }
    }
}
finally {
    $Repo.Close()
}
DbaReportHelpers.ps1: the shared helper file (save it in the same folder as the script). This version adds the functions the newer collectors use; it replaces the older copy and the earlier scripts still work with it.
# 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()
}

function Invoke-SqlQuerySet {
    # Like Invoke-SqlQuery, but returns a DataSet: one DataTable per result set.
    param($Connection, [string]$Sql, [hashtable]$Parameters = @{})

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

function Assert-InventoryTable {
    # Stops the run early with a clear message if 01_Create_Tables.sql hasn't been run.
    param($Connection, [Parameter(Mandatory)][string[]]$Table)

    foreach ($Name in $Table) {
        $Id = (Invoke-SqlQuery $Connection "SELECT OBJECT_ID(@Name, 'U') AS Id" @{ Name = $Name }).Rows[0].Id
        if ($Id -is [System.DBNull]) {
            throw "Table $Name is missing. Run sql\01_Create_Tables.sql in the DBA database first."
        }
    }
}

function Add-DataTableRows {
    # Inserts every row of a DataTable, adding the run's CaptureDate and ServerName. Returns the row count.
    param(
        $Connection,
        [Parameter(Mandatory)][string]$Table,
        [Parameter(Mandatory)]$Data,
        [Parameter(Mandatory)][string]$ServerName,
        [Parameter(Mandatory)][datetime]$CaptureDate
    )

    foreach ($Source in $Data.Rows) {
        $Row = [ordered]@{ CaptureDate = $CaptureDate; ServerName = $ServerName }
        foreach ($Column in $Data.Columns.ColumnName) {
            $Row[$Column] = $Source[$Column]
        }
        Add-InventoryRow -Connection $Connection -Table $Table -Row $Row
    }
    $Data.Rows.Count
}

function Write-CollectionLog {
    # One row per server per collection per run, so a server that failed is never just missing.
    param(
        $Connection,
        [Parameter(Mandatory)][string]$Collection,
        [Parameter(Mandatory)][string]$ServerName,
        [Parameter(Mandatory)][datetime]$CaptureDate,
        [Parameter(Mandatory)][ValidateSet('SUCCESS', 'PARTIAL', 'FAILED')][string]$Status,
        [int]$Rows = 0,
        [string]$ErrorMessage
    )

    Add-InventoryRow -Connection $Connection -Table 'dbo.CollectionLog' -Row ([ordered]@{
        CaptureDate      = $CaptureDate
        Collection       = $Collection
        ServerName       = $ServerName
        CollectionStatus = $Status
        RowsCollected    = $Rows
        ErrorMessage     = $ErrorMessage
    })
}

The tables

The script stops with a clear message if these are missing. Run this once before the first collection.

-- Run once in the DBA database on the inventory server, before the first run of
-- the script. Safe to run again: each table is only created if it's missing.
IF DB_ID(N'DBA') IS NULL
    CREATE DATABASE DBA;
GO

USE DBA;
GO

-- Shared by the collectors: one row per server per run, so a server that
-- failed is never just missing from the report
IF OBJECT_ID(N'dbo.CollectionLog', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.CollectionLog
    (
        CollectionLogID  BIGINT IDENTITY(1,1) NOT NULL
                         CONSTRAINT PK_CollectionLog PRIMARY KEY NONCLUSTERED,
        CaptureDate      DATETIME2(0)  NOT NULL,
        Collection       VARCHAR(30)   NOT NULL,   -- Integrity, WindowsPatch, SqlPatch, Security, Capacity
        ServerName       NVARCHAR(128) NOT NULL,
        CollectionStatus VARCHAR(20)   NOT NULL,   -- SUCCESS, PARTIAL, FAILED
        RowsCollected    INT           NULL,
        ErrorMessage     NVARCHAR(MAX) NULL
    );
    CREATE CLUSTERED INDEX CX_CollectionLog
        ON dbo.CollectionLog (Collection, ServerName, CaptureDate);
END;
GO

IF OBJECT_ID(N'dbo.DatabaseFileInventory', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.DatabaseFileInventory
    (
        CaptureDate      DATETIME2(0)  NOT NULL,
        ServerName       NVARCHAR(128) NOT NULL,
        DatabaseName     NVARCHAR(128) NOT NULL,
        LogicalName      NVARCHAR(128) NOT NULL,
        FileType         NVARCHAR(60)  NULL,        -- ROWS, LOG, FILESTREAM
        PhysicalName     NVARCHAR(260) NULL,
        VolumeMountPoint NVARCHAR(260) NULL,
        SizeMB           DECIMAL(18,2) NULL,
        UsedMB           DECIMAL(18,2) NULL,        -- NULL when the database couldn't be opened
        GrowthMB         DECIMAL(18,2) NULL,        -- fixed growth; 0 = growth off
        GrowthPct        INT           NULL,        -- percentage growth
        MaxSizeMB        DECIMAL(18,2) NULL,        -- NULL = unlimited
        VolumeTotalMB    DECIMAL(18,2) NULL,
        VolumeFreeMB     DECIMAL(18,2) NULL
    );
    CREATE CLUSTERED INDEX CX_DatabaseFileInventory
        ON dbo.DatabaseFileInventory (ServerName, DatabaseName, CaptureDate);
END;
GO

The report queries

Run these in the DBA database. The forecast needs at least two weeks of daily runs before it shows anything and is only reliable after a couple of months; the yearly figures need a year.

/*
CapacityReport.sql - run in the DBA database.
Quarterly for the IT manager and finance; the volume forecast is also worth a
monthly look. Needs at least a few weeks of daily runs before the growth
figures mean anything, and a year for the annual ones.
*/
USE DBA;

/* 1. Volume forecast: how long until each SQL Server volume is full -------- */
WITH Vol AS
(
    SELECT DISTINCT CaptureDate, ServerName, VolumeMountPoint, VolumeTotalMB, VolumeFreeMB
    FROM dbo.DatabaseFileInventory
    WHERE VolumeMountPoint IS NOT NULL
),
Newest AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ServerName, VolumeMountPoint ORDER BY CaptureDate DESC) AS RN
    FROM Vol
),
Baseline AS
(
    -- the reading closest to 90 days ago (at least 14 days old)
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ServerName, VolumeMountPoint
                                 ORDER BY ABS(DATEDIFF(DAY, CaptureDate, DATEADD(DAY, -90, SYSDATETIME())))) AS RN
    FROM Vol
    WHERE CaptureDate <= DATEADD(DAY, -14, SYSDATETIME())
)
SELECT
    N.ServerName,
    N.VolumeMountPoint,
    CAST(N.VolumeTotalMB / 1024 AS DECIMAL(18,1))                   AS TotalGB,
    CAST(N.VolumeFreeMB  / 1024 AS DECIMAL(18,1))                   AS FreeGB,
    CAST(100.0 * N.VolumeFreeMB / NULLIF(N.VolumeTotalMB, 0) AS DECIMAL(5,1)) AS FreePct,
    G.UsedGBPerMonth,
    M.MonthsUntilFull,
    DATEDIFF(DAY, B.CaptureDate, N.CaptureDate)                     AS DaysOfHistory,
    CASE
        WHEN B.CaptureDate IS NULL   THEN 'NOT ENOUGH HISTORY'
        WHEN M.MonthsUntilFull < 3   THEN 'ORDER DISK NOW'
        WHEN M.MonthsUntilFull < 6   THEN 'PLAN DISK'
        WHEN M.MonthsUntilFull < 12  THEN 'WATCH'
        ELSE 'OK'
    END AS CapacityStatus
FROM Newest AS N
LEFT JOIN Baseline AS B
       ON B.ServerName = N.ServerName AND B.VolumeMountPoint = N.VolumeMountPoint AND B.RN = 1
CROSS APPLY
(
    SELECT UsedGBPerMonth = CAST((B.VolumeFreeMB - N.VolumeFreeMB) / 1024.0
                                 / NULLIF(DATEDIFF(DAY, B.CaptureDate, N.CaptureDate), 0) * 30.4
                                 AS DECIMAL(18,2))
) AS G
CROSS APPLY
(
    SELECT MonthsUntilFull = CAST(CASE WHEN G.UsedGBPerMonth > 0
                                       THEN N.VolumeFreeMB / 1024.0 / G.UsedGBPerMonth END AS DECIMAL(9,1))
) AS M
WHERE N.RN = 1
ORDER BY CASE WHEN M.MonthsUntilFull IS NULL THEN 1 ELSE 0 END, M.MonthsUntilFull, N.ServerName;

/* 2. Database growth: data actually used, over 90 days and a year ---------- */
WITH Db AS
(
    SELECT CaptureDate, ServerName, DatabaseName,
           SUM(CASE WHEN FileType = 'ROWS' THEN UsedMB END) AS DataUsedMB,
           SUM(SizeMB)                                      AS AllocatedMB
    FROM dbo.DatabaseFileInventory
    GROUP BY CaptureDate, ServerName, DatabaseName
),
Ranked AS
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY ServerName, DatabaseName ORDER BY CaptureDate DESC) AS Newest,
           ROW_NUMBER() OVER (PARTITION BY ServerName, DatabaseName
                              ORDER BY ABS(DATEDIFF(DAY, CaptureDate, DATEADD(DAY, -90,  SYSDATETIME())))) AS Near90,
           ROW_NUMBER() OVER (PARTITION BY ServerName, DatabaseName
                              ORDER BY ABS(DATEDIFF(DAY, CaptureDate, DATEADD(DAY, -365, SYSDATETIME())))) AS Near365
    FROM Db
    WHERE DataUsedMB IS NOT NULL
)
SELECT
    N.ServerName,
    N.DatabaseName,
    CAST(N.DataUsedMB  / 1024 AS DECIMAL(18,2))                                  AS DataUsedGB,
    CAST(N.AllocatedMB / 1024 AS DECIMAL(18,2))                                  AS AllocatedGB,
    CAST((N.DataUsedMB - Q.DataUsedMB) / 1024
         / NULLIF(DATEDIFF(DAY, Q.CaptureDate, N.CaptureDate), 0) * 30.4 AS DECIMAL(18,2)) AS GrowthGBPerMonth,
    CASE WHEN Y.CaptureDate <= DATEADD(DAY, -300, N.CaptureDate)
         THEN CAST((N.DataUsedMB - Y.DataUsedMB) / 1024 AS DECIMAL(18,2)) END    AS GrowthGBLastYear,
    CASE WHEN Y.CaptureDate <= DATEADD(DAY, -300, N.CaptureDate)
         THEN CAST(100.0 * (N.DataUsedMB - Y.DataUsedMB) / NULLIF(Y.DataUsedMB, 0) AS DECIMAL(9,1)) END AS GrowthPctLastYear
FROM Ranked AS N
LEFT JOIN Ranked AS Q ON Q.ServerName = N.ServerName AND Q.DatabaseName = N.DatabaseName
                     AND Q.Near90 = 1 AND Q.CaptureDate <= DATEADD(DAY, -14, N.CaptureDate)
LEFT JOIN Ranked AS Y ON Y.ServerName = N.ServerName AND Y.DatabaseName = N.DatabaseName AND Y.Near365 = 1
WHERE N.Newest = 1
ORDER BY GrowthGBPerMonth DESC;

/* 3. Server summary: the table for the quarterly pack --------------------- */
WITH LatestRun AS
(
    SELECT ServerName, MAX(CaptureDate) AS CaptureDate
    FROM dbo.CollectionLog
    WHERE Collection = 'Capacity' AND CollectionStatus = 'SUCCESS'
    GROUP BY ServerName
),
Files AS
(
    SELECT F.*
    FROM dbo.DatabaseFileInventory AS F
    JOIN LatestRun AS R ON R.ServerName = F.ServerName AND R.CaptureDate = F.CaptureDate
),
Volumes AS
(
    SELECT DISTINCT ServerName, VolumeMountPoint, VolumeTotalMB, VolumeFreeMB
    FROM Files
    WHERE VolumeMountPoint IS NOT NULL
)
SELECT
    F.ServerName,
    COUNT(DISTINCT F.DatabaseName)                                                  AS Databases,
    CAST(SUM(CASE WHEN F.FileType = 'ROWS' THEN F.SizeMB END) / 1024 AS DECIMAL(18,1)) AS DataAllocatedGB,
    CAST(SUM(CASE WHEN F.FileType = 'ROWS' THEN F.UsedMB END) / 1024 AS DECIMAL(18,1)) AS DataUsedGB,
    CAST(SUM(CASE WHEN F.FileType = 'LOG'  THEN F.SizeMB END) / 1024 AS DECIMAL(18,1)) AS LogAllocatedGB,
    (SELECT CAST(SUM(V.VolumeTotalMB) / 1024 AS DECIMAL(18,1)) FROM Volumes AS V WHERE V.ServerName = F.ServerName) AS DiskTotalGB,
    (SELECT CAST(SUM(V.VolumeFreeMB)  / 1024 AS DECIMAL(18,1)) FROM Volumes AS V WHERE V.ServerName = F.ServerName) AS DiskFreeGB
FROM Files AS F
GROUP BY F.ServerName
ORDER BY F.ServerName;

/* 4. File settings that make growth unpredictable ------------------------- */
WITH LatestRun AS
(
    SELECT ServerName, MAX(CaptureDate) AS CaptureDate
    FROM dbo.CollectionLog
    WHERE Collection = 'Capacity' AND CollectionStatus = 'SUCCESS'
    GROUP BY ServerName
)
SELECT
    F.ServerName, F.DatabaseName, F.LogicalName, F.FileType,
    CAST(F.SizeMB / 1024 AS DECIMAL(18,2)) AS SizeGB,
    F.GrowthMB, F.GrowthPct, F.MaxSizeMB,
    CONCAT_WS(', ',
        CASE WHEN F.GrowthPct IS NOT NULL                                  THEN 'Percentage growth' END,
        CASE WHEN F.GrowthMB = 0                                           THEN 'Autogrowth off' END,
        CASE WHEN F.GrowthMB > 0 AND F.GrowthMB < 64 AND F.SizeMB > 1024   THEN 'Growth under 64 MB on a 1 GB+ file' END,
        CASE WHEN F.MaxSizeMB IS NOT NULL AND F.UsedMB > 0.9 * F.MaxSizeMB THEN 'Over 90% of max size' END,
        CASE WHEN F.GrowthMB > 0 AND F.VolumeFreeMB < F.GrowthMB           THEN 'Next growth will not fit on the volume' END
    ) AS Problems
FROM dbo.DatabaseFileInventory AS F
JOIN LatestRun AS R ON R.ServerName = F.ServerName AND R.CaptureDate = F.CaptureDate
WHERE F.GrowthPct IS NOT NULL
   OR F.GrowthMB = 0
   OR (F.GrowthMB > 0 AND F.GrowthMB < 64 AND F.SizeMB > 1024)
   OR (F.MaxSizeMB IS NOT NULL AND F.UsedMB > 0.9 * F.MaxSizeMB)
   OR (F.GrowthMB > 0 AND F.VolumeFreeMB < F.GrowthMB)
ORDER BY F.ServerName, F.DatabaseName, F.LogicalName;

Watch out for

  • The forecast is a straight line. It assumes the next three months look like the last three. Month-end, year-end, a data migration or an archive purge will all bend it, so read it alongside what you know is coming.
  • It measures the drive, not just the databases. Volume growth comes from free space, so backups, exports or anything else written to the same drive counts. If a data drive is filling faster than its databases are growing, that’s worth knowing too.
  • Only drives with database files. sys.dm_os_volume_stats only sees volumes SQL Server has files on. Backup drives need the disk space collector.
  • Percentage growth. 10% of a 1 TB file is 100 GB in one go. Fixed growth in MB is predictable; set it to something sensible for the file, a few hundred MB to a few GB for data.
  • Log files grow slowly without instant file initialisation. Data files can skip zeroing if the service account has the Perform Volume Maintenance Tasks right. Log files are always zeroed, apart from growths up to 64 MB on SQL Server 2022 and later, so big log growths stall the database while they happen.

Where it fits

This is part of my Auditing section, alongside the disk space alerts for today’s problems and the database inventory for each database’s settings. Sizing and file settings for a new server are covered in the SQL Server DBA Handbook.

The other Auditing reports: Licensing | Availability: ping and response time | Disk space alerts | Server inventory | Database inventory | Backups | SSIS errors | Integrity checks | Windows patching | SQL Server patching | Security

Clive Roberts DBA