The Drive is Full. When Did That Happen?
Storage doesn’t run out suddenly. It creeps.
The question is always: how fast is it growing, and when will we run out?
Autogrowth is not a storage strategy. It’s a safety net — and every time it fires, SQL Server pauses to extend the file while your users wait.
These queries answer three things:
- How big were our databases 6 months ago vs now?
- How much allocated space is currently unused?
- Are our autogrowth settings going to hurt us?
What This Query Does
Part 1 — Backup Size History
msdb.dbo.backupset records every full backup SQL Server has ever taken (until history is purged).
Plotting full backup sizes over 6 months gives a reliable growth curve — not just current allocation, but actual data written.
Part 2 — Current File Allocation vs Usage
sys.master_files shows the allocated file size.
The FILEPROPERTY function returns how much of that allocation is actually used.
The gap between allocated and used is pre-reserved white space. The autogrowth column tells you what happens next time the file fills up — and whether it will grow by 1MB every time (a classic misconfiguration that fires constantly) or by something sensible.
Database Growth Trends Script
Loading…
Gareth Winterman