Tracking Database Growth Over Time: The Daily Snapshot Method I Use

There are plenty of ways to check a database's current size - sp_spaceused, the Disk Usage report in SSMS, or querying sys.master_files directly. All of those answer "how big is it right now?" What they don't answer on their own is "how fast is it growing, and is that normal?" That's the question that actually matters for capacity planning - deciding when to request more storage, which databases need archiving strategies, and which ones are quietly heading toward a full disk before anyone notices.

This is the approach I put together today to answer that: log a daily snapshot of file sizes into a history table, then query that history to measure growth over any period I choose.

Step 1: A daily snapshot job

The core idea is simple - every day, capture the current state of every database file on the instance and append it to a table, rather than overwriting it. sys.master_files already has everything needed for a single point-in-time snapshot: file size, max size, growth setting, and whether that growth is a fixed amount or a percentage.

INSERT INTO DatabaseFileSize
(
    [database_id],
    [file_id],
    [file_type_desc],
    [name],
    [physical_name],
    [state_desc],
    [size],
    [max_size],
    [growth],
    [is_sparse],
    [is_percent_growth],
    [collect_date]
)
SELECT
    [database_id],
    [file_id],
    [type_desc],
    [name],
    [physical_name],
    [state_desc],
    [size],
    [max_size],
    [growth],
    [is_sparse],
    [is_percent_growth],
    GETDATE()
FROM sys.master_files;

I wrap this in a stored procedure and schedule it as a daily SQL Agent job. A few things worth calling out about the source data itself:

• size is in 8 KB pages, not MB - it needs converting (size * 8 / 1024) when reporting.
• file_id = 1 is conventionally the primary data file, but a database can have multiple data files across filegroups, and file_id = 2 is typically the log file. Filtering only on file_id = 1, as I do in the comparison query below, gives you primary data file growth - it won't catch a secondary data file or a runaway transaction log growing unchecked.
• is_percent_growth matters more than it looks - percentage-based autogrowth on a large file can mean each growth event adds a very large chunk at once, which is worth flagging on its own, separate from the growth trend itself.

Step 2: Comparing snapshots to see growth

With a few weeks or months of daily snapshots collected, this is the query I used today to compare size between two specific dates and flag databases that grew by more than 1 GB:

USE epaymis;

SELECT
    a.DatabaseName,
    a.DBSizeInMB,
    b.DBSizeInMB,
    (b.DBSizeInMB - a.DBSizeInMB) AS size_increase_in_MB,
    (b.DBSizeInMB - a.DBSizeInMB) / 1024 AS size_increase_in_GB
FROM
    (SELECT
        DB_NAME(database_id) AS DatabaseName,
        collect_date AS CollectionDate,
        ((SUM(size)) * 8) / 1024 AS DBSizeInMB
     FROM DatabaseFileSize
     WHERE CONVERT(DATE, collect_date, 101) IN ('2026-05-03') AND File_id = 1
     GROUP BY database_id, collect_date) a,
    (SELECT
        DB_NAME(database_id) AS DatabaseName,
        collect_date AS CollectionDate,
        ((SUM(size)) * 8) / 1024 AS DBSizeInMB
     FROM DatabaseFileSize
     WHERE CONVERT(DATE, collect_date, 101) IN ('2026-05-22') AND File_id = 1
     GROUP BY database_id, collect_date) b
WHERE a.DatabaseName = b.DatabaseName
    AND (b.DBSizeInMB - a.DBSizeInMB) > 1000
ORDER BY 4 DESC;

This did exactly what I needed - it took the guesswork out of "which databases grew the most between these two dates" and gave me a ranked list instead of eyeballing raw sizes across many databases.

Where I'd take this further

This got the job done today, but sitting back and looking at it, there are a few real improvements worth making before I'd call this a finished monitoring solution rather than a one-off report.

1. Replace the hardcoded self-join with a window function

The two-subquery self-join works, but it only compares exactly two dates I have to type in every time, and it doesn't scale well to "show me growth for every database over the last 30 days." LAG() over a date-ordered partition solves both:

SELECT
    DatabaseName,
    CollectionDate,
    DBSizeInMB,
    DBSizeInMB - LAG(DBSizeInMB) OVER (
        PARTITION BY DatabaseName ORDER BY CollectionDate
    ) AS SizeChangeInMB
FROM (
    SELECT
        DB_NAME(database_id) AS DatabaseName,
        CONVERT(DATE, collect_date) AS CollectionDate,
        ((SUM(size)) * 8) / 1024 AS DBSizeInMB
    FROM DatabaseFileSize
    WHERE File_id = 1
    GROUP BY database_id, CONVERT(DATE, collect_date)
) AS DailySizes
ORDER BY DatabaseName, CollectionDate;

This gives day-over-day growth for every database in one pass, with no dates to hardcode, and it's just as easy to adapt into week-over-week or month-over-month by grouping on a truncated date first.

2. Track the log file, not just the data file

Filtering to File_id = 1 only tells half the story. A transaction log that's growing uncontrollably (usually from a stuck replication, an open transaction, or a backup job that stopped running) is one of the more common causes of an unexpected "disk full" page at 2 AM. I'd run the same growth comparison without the File_id = 1 filter, grouped by file_type_desc instead, so ROWS and LOG growth show up as separate trends.

3. Capture autogrowth events, not just daily deltas

A daily snapshot can tell you a file grew by 2 GB since yesterday, but not how many separate autogrowth events caused that, or whether growth is happening in painful percentage-based jumps. SQL Server's Extended Events session already captures this in real time via the data_file_auto_grow and log_file_auto_grow events, which is a much more precise complement to the daily snapshot approach:

CREATE EVENT SESSION [FileAutoGrowthTracking] ON SERVER
ADD EVENT sqlserver.database_file_size_change,
ADD EVENT sqlserver.log_file_auto_grow
ADD TARGET package0.event_file
    (SET filename = N'FileAutoGrowthTracking.xel');

ALTER EVENT SESSION [FileAutoGrowthTracking] ON SERVER STATE = START;

Between the daily trend table (good for long-term capacity planning) and Extended Events (good for catching exactly when and how growth happened), you get both the big picture and the fine detail.

4. Turn the threshold check into an actual alert

Right now, seeing which databases crossed the 1 GB growth threshold requires someone to run the comparison query. The more useful version of this is a scheduled job that runs the comparison automatically and emails a result set when anything crosses the threshold, using sp_send_dbmail, so growth anomalies surface on their own instead of depending on someone remembering to check.

5. Manage the history table itself

A daily insert into DatabaseFileSize across many databases and files adds up over months and years. I'd add an index on (database_id, file_id, collect_date) to keep the comparison queries fast, and a retention/purge job that archives or deletes rows past a defined age - there's rarely a need to keep daily granularity going back multiple years once trend reporting has been done on it.

Putting it together

The daily snapshot approach solved the immediate problem - I now have real history instead of a single point-in-time size - and it's a solid foundation. The next iteration is less about the core idea and more about making it self-sufficient: window functions instead of hardcoded dates, covering the log file as well as the data file, capturing real autogrowth events for precision, and turning threshold checks into alerts instead of manual queries.

More posts on SQL Server performance, security, and AI-assisted DBA automation are on the way.