Part of the DBA-Tools Project.
Two Different Questions: “Is It Close?” and “When Will It Hit?”
“How close is this database to its configured size limit?” and “at its current growth rate, when will it actually get there?” are two different questions, and answering them needs two different scripts. The first is a snapshot you can run right now, no history required. The second needs a trend, which means it needs history someone was already collecting.
This post covers both. Database Growth Risk checks every database against its configured max file size right now and flags anything near or at that limit. Database Growth Forecast goes further: using the repo’s growth collector history, it calculates each file’s MB/day growth rate and projects a real date for hitting the limit.
Run Growth Risk everywhere, always. Run Growth Forecast once the collector has been running long enough to have real history to project from.
Why Growth Risk and Forecast Matter
- A database with
UNLIMITEDgrowth on its files isn’t actually unlimited, the disk underneath still has a real ceiling, this is a distinct risk fromAT_LIMIT/NEAR_LIMIT, not a safe case to ignore - A database sitting at 85%+ of a configured max file size is one bulk load away from hitting a hard wall: inserts start failing the moment autogrow can’t proceed
- Raw current size tells you nothing about urgency, a database at 40% of its limit growing 50 MB/day is a very different problem than one at 85% growing 2 MB/day
- Forecasting needs a real growth rate over a real time window, not a guess, that’s what the collector history in
DBAMonitor.collector.DatabaseGrowthCurrentprovides
When to Run These Scripts
- Growth Risk: routine health checks, on every database, it needs no setup and runs in milliseconds
- Growth Forecast: capacity planning conversations, once the growth collector has 48+ hours of history, to put an actual date on a trend rather than a feeling
- Before a licensing or hardware conversation where “we’ll need more space eventually” needs a number attached
- After Growth Risk flags
NEAR_LIMITorAT_LIMIT, to see whether that’s a slow trend or an imminent one
The Scripts
Get-DatabaseGrowthRisk — Right Now, No Setup Required
/*
Script Name : Get-DatabaseGrowthRisk
Category : storage-capacity-management
Purpose : Flag databases approaching their configured file size limits.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-database-growth-risk-and-forecast/)
Requires : VIEW ANY DATABASE
HealthCheck : Yes
*/
-- Blog: https://sqldba.blog/dba-scripts-get-database-growth-risk-and-forecast/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
WITH db_sizes AS (
SELECT
d.name AS database_name,
CAST(SUM(CASE WHEN mf.type_desc = 'ROWS'
THEN mf.size * 8.0 / 1024 END) AS DECIMAL(18,2)) AS data_mb,
CAST(SUM(CASE WHEN mf.type_desc = 'LOG'
THEN mf.size * 8.0 / 1024 END) AS DECIMAL(18,2)) AS log_mb,
CAST(SUM(CASE WHEN mf.max_size = -1 THEN 0
ELSE mf.max_size * 8.0 / 1024 END) AS DECIMAL(18,2)) AS growth_limit_mb
FROM sys.databases AS d
LEFT JOIN sys.master_files AS mf ON d.database_id = mf.database_id
WHERE d.database_id > 4
GROUP BY d.name
)
SELECT
database_name,
data_mb,
log_mb,
CAST(data_mb + log_mb AS DECIMAL(18,2)) AS total_mb,
growth_limit_mb,
CASE
WHEN growth_limit_mb = 0 THEN 'UNLIMITED'
WHEN data_mb + log_mb >= growth_limit_mb THEN 'AT_LIMIT'
WHEN data_mb + log_mb >= growth_limit_mb * 0.85 THEN 'NEAR_LIMIT'
ELSE 'OK'
END AS growth_status
FROM db_sizes
ORDER BY total_mb DESC;
growth_limit_mb = 0 means every file on that database has max_size = -1 (unlimited growth, so this reports as UNLIMITED), not that the database has no room. Treat UNLIMITED as its own category to check against actual disk free space, not as automatically safe.
Get-DatabaseGrowthForecast — Real Trend, Needs Collector History
/*
Script Name : Get-DatabaseGrowthForecast
Category : storage-capacity-management
Purpose : Project when database files will exhaust their configured size limits,
using historical file size changes recorded by the DatabaseGrowth temporal collector.
Calculates MB/day growth rate from the first and last observed size within the
window, then projects forward to the configured file limit.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-database-growth-risk-and-forecast/)
Requires : SELECT on DBAMonitor.collector.DatabaseGrowthCurrent (and its history table)
Depends On : sql\collectors\Generate-CollectorJob-DatabaseGrowth.sql
(temporal collector must be installed and collecting for at least 48 hours)
Notes : Projects file-limit exhaustion only — not physical disk exhaustion.
One-off bulk loads within @WindowDays inflate the growth rate.
Reduce @WindowDays to 7 to focus on recent steady-state growth only.
Files with no size change in the window appear as STABLE (mb_per_day = 0).
Requires SQL Server 2016 or later.
*/
-- Blog: https://sqldba.blog/dba-scripts-get-database-growth-risk-and-forecast/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
DECLARE @WindowDays int = 30;
DECLARE @WindowStart DATETIME2 = DATEADD(DAY, -@WindowDays, SYSUTCDATETIME());
DECLARE @WindowEnd DATETIME2 = SYSUTCDATETIME();
-- ── Existence checks ──────────────────────────────────────────────────────────
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = N'DBAMonitor')
BEGIN
RAISERROR('DBAMonitor database not found. Run sql\collectors\Generate-CollectorJob-DatabaseGrowth.sql to set up the growth collector.', 16, 1);
RETURN;
END
IF NOT EXISTS (
SELECT 1 FROM DBAMonitor.sys.objects o
JOIN DBAMonitor.sys.schemas s ON s.schema_id = o.schema_id
WHERE o.name = N'DatabaseGrowthCurrent' AND s.name = N'collector')
BEGIN
RAISERROR('collector.DatabaseGrowthCurrent not found in DBAMonitor. Run the collector generator and allow at least one job run.', 16, 1);
RETURN;
END
IF NOT EXISTS (SELECT 1 FROM DBAMonitor.collector.DatabaseGrowthCurrent)
BEGIN
RAISERROR('collector.DatabaseGrowthCurrent has no data yet. Allow the collection job to run at least once before forecasting.', 16, 1);
RETURN;
END
-- ── Forecast ──────────────────────────────────────────────────────────────────
;WITH history AS (
SELECT
database_name,
logical_name,
file_type,
file_size_mb,
growth_limit_mb,
SysStartTime AS snapshot_time
FROM DBAMonitor.collector.DatabaseGrowthCurrent
FOR SYSTEM_TIME BETWEEN @WindowStart AND @WindowEnd
),
ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY database_name, logical_name
ORDER BY snapshot_time ASC) AS rn_asc,
ROW_NUMBER() OVER (PARTITION BY database_name, logical_name
ORDER BY snapshot_time DESC) AS rn_desc,
COUNT(*) OVER (PARTITION BY database_name, logical_name) AS snapshot_count
FROM history
),
first_last AS (
SELECT
database_name,
logical_name,
file_type,
MAX(growth_limit_mb) AS growth_limit_mb,
MAX(snapshot_count) AS snapshot_count,
MIN(snapshot_time) AS first_time,
MIN(CASE WHEN rn_asc = 1 THEN file_size_mb END) AS first_size_mb,
MAX(snapshot_time) AS last_time,
MIN(CASE WHEN rn_desc = 1 THEN file_size_mb END) AS current_size_mb
FROM ranked
GROUP BY database_name, logical_name, file_type
),
projections AS (
SELECT
database_name,
logical_name,
file_type,
snapshot_count,
growth_limit_mb,
current_size_mb,
CAST(DATEDIFF(hour, first_time, last_time) / 24.0 AS decimal(6,1)) AS days_observed,
CASE
WHEN DATEDIFF(hour, first_time, last_time) > 0
THEN (current_size_mb - first_size_mb) /
(DATEDIFF(hour, first_time, last_time) / 24.0)
ELSE 0
END AS mb_per_day
FROM first_last
)
SELECT
database_name,
logical_name,
file_type,
snapshot_count,
days_observed,
CAST(current_size_mb AS decimal(10,1)) AS current_size_mb,
CAST(mb_per_day AS decimal(10,2)) AS mb_per_day,
growth_limit_mb,
CASE
WHEN mb_per_day > 0 AND growth_limit_mb IS NOT NULL
THEN CAST((growth_limit_mb - current_size_mb) / mb_per_day AS int)
ELSE NULL
END AS days_to_limit,
CASE
WHEN mb_per_day > 0 AND growth_limit_mb IS NOT NULL
THEN DATEADD(day,
CAST((growth_limit_mb - current_size_mb) / mb_per_day AS int),
GETDATE())
ELSE NULL
END AS projected_limit_date,
CASE
WHEN mb_per_day > 0 AND growth_limit_mb IS NOT NULL
AND (growth_limit_mb - current_size_mb) / mb_per_day < 30 THEN 'CRITICAL'
WHEN mb_per_day > 0 AND growth_limit_mb IS NOT NULL
AND (growth_limit_mb - current_size_mb) / mb_per_day < 90 THEN 'WARNING'
WHEN mb_per_day > 0 AND growth_limit_mb IS NULL THEN 'UNLIMITED'
WHEN mb_per_day <= 0 THEN 'STABLE'
ELSE 'OK'
END AS forecast_status
FROM projections
ORDER BY
CASE
WHEN mb_per_day > 0 AND growth_limit_mb IS NOT NULL
AND (growth_limit_mb - current_size_mb) / mb_per_day < 30 THEN 1
WHEN mb_per_day > 0 AND growth_limit_mb IS NOT NULL
AND (growth_limit_mb - current_size_mb) / mb_per_day < 90 THEN 2
WHEN mb_per_day > 0 AND growth_limit_mb IS NULL THEN 3
WHEN mb_per_day <= 0 THEN 5
ELSE 4
END,
CASE WHEN mb_per_day > 0 AND growth_limit_mb IS NOT NULL
THEN (growth_limit_mb - current_size_mb) / mb_per_day
ELSE NULL END,
current_size_mb DESC;
How To Run From The Repo
Clone DBA Tools, initialize and run either script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Check every database against its configured file size limit, right now:
.\run.ps1 Get-DatabaseGrowthRisk
# Set up the growth collector (once), then forecast after 48+ hours of history:
.\run.ps1 Generate-CollectorJob-DatabaseGrowth
.\run.ps1 Get-DatabaseGrowthForecast
# To run against a remote sql server:
.\run.ps1 Get-DatabaseGrowthRisk -ServerInstance SQLSERVER01
These scripts live in the repo at:
sql/monitoring/disk-space/Get-DatabaseGrowthRisk.sqlsql/monitoring/disk-space/Get-DatabaseGrowthForecast.sql
Example Output
Real Growth Risk output from this lab instance, not staged (20 databases, condensed):
| database_name | total_mb | growth_limit_mb | growth_status |
|---|---|---|---|
| WatchtowerMetrics | 3762.19 | 2097152.00 | OK |
| GrowthLab | 3152.00 | 2097152.00 | OK |
| DemoDatabase | 2560.00 | 2097152.00 | OK |
| migdb_A9250FB6 | 950.00 | 2097152.00 | OK |
Every database on this lab box shows OK against a ~2TB configured limit, this box has never had a database genuinely close to its configured max size. That’s expected and worth showing honestly: OK across the board is the normal, healthy result most of the time this script runs.
Growth Forecast on this same instance correctly reports:
Msg 50000, Level 16, State 1
DBAMonitor database not found. Run sql\collectors\Generate-CollectorJob-DatabaseGrowth.sql to set up the growth collector.
That’s also the correct, honest result: this lab instance has never had the growth collector installed, so there’s no history to forecast from. This is exactly the guard-clause behavior the script is designed to show rather than fail unhelpfully, and it’s worth including here since it’s what most readers trying this script for the first time will actually see.
Understanding the Results
Growth Risk:
– OK — comfortable headroom against the configured limit
– NEAR_LIMIT — at or above 85% of the configured max file size, worth a closer look this week
– AT_LIMIT — already at or over the configured limit; further growth for that database has already failed or is about to
– UNLIMITED — no configured max size on any file; not automatically safe, check real disk free space instead
Growth Forecast (once collector history exists):
– mb_per_day — the actual observed growth rate over the window, not an estimate
– days_to_limit / projected_limit_date, only populated when a file is actively growing toward a configured limit
– STABLE — no net size change in the window; not the same as OK, a STABLE file at 95% of its limit is still worth watching, it just isn’t actively getting worse
– snapshot_count — low counts mean less confidence in the projection; a rate calculated from only 2-3 snapshots is noisier than one from daily snapshots over 30 days
Best Practices
- Run Growth Risk on every routine health check, it needs no setup and catches configured-limit problems immediately
- Set up the growth collector early, even before you need forecasting, so history is already there when a capacity conversation comes up
- Treat
UNLIMITEDas “check the disk,” not as “no risk,” the practical ceiling is still the volume underneath - When Growth Forecast shows a short
days_to_limit, sanity-check whether the window included a one-off bulk load before treating it as a steady trend; reducing@WindowDaysto 7 helps isolate recent steady-state growth
Related Scripts
You may also find these scripts useful:
- Storage and Capacity (hub)
- Database Free Space Summary
- Filegroup Space
- Disk Space
- Autogrowth History
- Database Sizes and Free Space
Frequently Asked Questions
What’s the difference between Growth Risk and Growth Forecast?
Growth Risk is a snapshot: is this database close to its configured limit right now? It needs no setup and runs instantly. Growth Forecast is a trend: at the current growth rate, when will it get there? It needs history from the repo’s growth collector, so it takes 48+ hours of setup before it’s useful.
Why does Growth Forecast need a separate collector installed first?
Projecting a growth rate needs at least two size observations over time. Generate-CollectorJob-DatabaseGrowth.sql sets up a scheduled job plus a temporal (system-versioned) table that records file sizes on a schedule, giving the forecast script real history to calculate mb_per_day from, instead of guessing from a single point-in-time size.
My database shows UNLIMITED, is that safe?
Not automatically. UNLIMITED means no file on that database has a configured max size, so growth_limit_mb is 0 and there’s no configured ceiling to project against. The real ceiling is still the disk underneath, check that with Disk Space.
Summary
“Is this database close to its limit” and “when will it get there” are genuinely different questions, and this post answers both. Growth Risk needs nothing but a live connection and flags configured-limit exposure right now. Growth Forecast needs real history from the growth collector, but turns that history into an actual projected date instead of a guess.
Run Growth Risk on every routine check. Set up the growth collector early enough that Growth Forecast has real history by the time a capacity conversation needs a number attached to it.
Leave a Reply