Estimating Table Growth for Next Year From Backup History

Next year's disk request should start with evidence you already have. Backup history gives a database trend, and current table space shows where to investigate table growth. Keep the difference clear, because a backup does not record each table's size.

Separate the Database Trend From Table Evidence

A full backup contains used database pages and backup overhead. Its uncompressed size is useful for tracking the whole database under a consistent backup policy. It is not the reserved size of a particular table. It also differs from allocated data files, log files, free file space, and the physical compressed backup file.

I separate those measures before making a forecast. Otherwise a compression change looks like a successful storage cleanup. Which capacity are you requesting: data volume, transaction log, backup repository, or all three? Each has a different retention and growth model. Start with one measure and label its unit. A chart with unlabeled gigabytes is a very confident question mark.

Keep the original backup records alongside the monthly summary. That makes an unusual point traceable to its backup scope and collection source. When someone questions a forecast, you can inspect the contributing record instead of guessing how the spreadsheet obtained its number.

Inspect the History You Actually Retained

The msdb backupset table stores backup metadata. Database full backups have type D. Partial backups record type P, and differential partial backups record type Q, so filtering on D already leaves them out. Exclude copy only backups for this series too, along with records that have no finish time. Also inspect has_backup_checksums and damaged backup flags when evaluating operational quality. History alone does not prove a backup can be restored.

SELECT database_name,backup_start_date,backup_finish_date,
       backup_size/1073741824.0 AS UncompressedGiB,
       compressed_backup_size/1073741824.0 AS StoredGiB,
       type,is_copy_only,has_backup_checksums,is_damaged
FROM msdb.dbo.backupset
WHERE type='D' AND backup_finish_date>=DATEADD(year,-1,GETDATE())
ORDER BY database_name,backup_finish_date;

Purged msdb history shortens your evidence window. Availability group backups spread records across instances. Imported backup headers can add records too. Gather the intended sources and deduplicate by backup identity before combining them. Database renames and restores also deserve a note because a name alone is not a durable identity for every historical workload.

Pick One Comparable Backup per Month

Use the last completed qualifying full backup in each calendar month. Exclude the current incomplete month from a year based forecast. The query stores a monthly series in a temporary table for the following examples. Run these blocks in the same connection. Use backup_size for the database trend and retain stored size separately for repository planning.

DECLARE @this_month date=DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1);
WITH ranked AS
(
    SELECT database_name,
           DATEFROMPARTS(YEAR(backup_finish_date),MONTH(backup_finish_date),1) AS MonthStart,
           CONVERT(decimal(19,3),backup_size/1073741824.0) AS FullGiB,
           ROW_NUMBER() OVER
           (PARTITION BY database_name,YEAR(backup_finish_date),MONTH(backup_finish_date)
            ORDER BY backup_finish_date DESC,backup_set_id DESC) AS rn
    FROM msdb.dbo.backupset
    WHERE type='D' AND is_copy_only=0 AND is_damaged=0
      AND backup_finish_date>=DATEADD(month,-12,@this_month)
      AND backup_finish_date<@this_month
)
SELECT database_name,MonthStart,FullGiB
INTO #MonthlyBackup
FROM ranked WHERE rn=1;
SELECT database_name,MonthStart,FullGiB,
       FullGiB-LAG(FullGiB) OVER
           (PARTITION BY database_name ORDER BY MonthStart) AS ChangeGiB,
       DATEDIFF(month,LAG(MonthStart) OVER
           (PARTITION BY database_name ORDER BY MonthStart),MonthStart) AS MonthsSincePrior
FROM #MonthlyBackup
ORDER BY database_name,MonthStart;

A change spanning missing months is not a single month change. MonthsSincePrior makes that gap visible. Check scheduled backup policy, compression, encryption, and included filegroups before interpreting jumps. Investigate unusual decreases too. Archiving, deletion, rebuilds, and a different backup scope can all change the line without representing steady business growth.

Project Twelve Months With a Stated Assumption

This simple forecast uses the first and last retained monthly points. Divide their size difference by the elapsed calendar months. Then extend that linear rate twelve months beyond the last point. It is an assumption you can inspect, not a statistical guarantee. A single retained month cannot establish a rate.

WITH endpoints AS
(
    SELECT database_name,MonthStart,FullGiB,
           ROW_NUMBER() OVER(PARTITION BY database_name ORDER BY MonthStart) AS first_rn,
           ROW_NUMBER() OVER(PARTITION BY database_name ORDER BY MonthStart DESC) AS last_rn
    FROM #MonthlyBackup
), paired AS
(
    SELECT database_name,
           MAX(CASE WHEN first_rn=1 THEN MonthStart END) AS FirstMonth,
           MAX(CASE WHEN last_rn=1 THEN MonthStart END) AS LastMonth,
           MAX(CASE WHEN first_rn=1 THEN FullGiB END) AS FirstGiB,
           MAX(CASE WHEN last_rn=1 THEN FullGiB END) AS LastGiB
    FROM endpoints GROUP BY database_name
)
SELECT database_name,FirstMonth,LastMonth,LastGiB,
       (LastGiB-FirstGiB)/NULLIF(DATEDIFF(month,FirstMonth,LastMonth),0) AS MonthlyGiB,
       LastGiB+12*(LastGiB-FirstGiB)
           /NULLIF(DATEDIFF(month,FirstMonth,LastMonth),0) AS ProjectedGiB
FROM paired;

Do not silently turn a negative forecast into a storage promise. A sustained archive policy and a one time cleanup mean different things. Compare the endpoint rate with individual monthly deltas. Add business expectations for a new customer rollout, retention change, or seasonal import. Show base and higher growth assumptions separately instead of hiding uncertainty inside one number.

Find Which Tables Drive Table Growth

Run this in the database being investigated. Count rows only from the heap or clustered index to avoid counting the same rows again through every nonclustered index. Sum reserved pages across all indexes for total table allocation. These are approximate metadata row counts, useful for inventory rather than transactionally exact reconciliation.

SELECT s.name AS SchemaName,t.name AS TableName,
       SUM(CASE WHEN p.index_id IN(0,1) THEN p.row_count ELSE 0 END) AS ApproxRows,
       SUM(p.reserved_page_count)*8.0/1024 AS ReservedMiB,
       SUM(p.used_page_count)*8.0/1024 AS UsedMiB
FROM sys.dm_db_partition_stats AS p
JOIN sys.tables AS t ON t.object_id=p.object_id
JOIN sys.schemas AS s ON s.schema_id=t.schema_id
WHERE t.is_ms_shipped=0
GROUP BY s.name,t.name
ORDER BY ReservedMiB DESC;

A large table is a place to inspect, not proof of recent growth. Reserved space includes room that is not currently used. Large objects and extra indexes also contribute. Memory optimized storage needs its own inventory. The backup trend and this snapshot answer different questions, and neither should be renamed to make the report sound more precise.

Record Table Growth With Regular Snapshots

Backup history cannot reconstruct last year's table sizes. Start a scheduled snapshot containing collection time, database identity, object identity, approximate rows, reserved pages, and used pages. Store it in an administrative database with a retention policy. Compare the same object over time, while tracking drops, recreations, and schema moves explicitly.

I check both row growth and bytes per row. A wider payload can increase storage while row counts remain steady. An added index can do the same. Investigate the largest changes against release dates and retention jobs. That turns table growth from a guess based on current size into a series with a clear explanation.

Translate Growth Into a Capacity Request

Database pages are only part of the request. Include allocated free space, expected autogrowth, index maintenance workspace, tempdb activity, and the transaction log's peak requirement. Backup repository capacity needs full, differential, and log retention plus compression assumptions. Count replicated copies where they consume separate storage. Keep these lines visible in the request.

Use your own measurements to choose headroom. Document how long procurement and provisioning take, then check capacity at that lead time. A forecast that alerts after the disk is full has impressive hindsight. Test restore storage too, because recovery can require a second copy alongside the running database.

Revisit the Table Growth Forecast When the Workload Changes

Schedule a review of the monthly series and the new table snapshots. Save the forecast assumptions with its collection date so later comparisons remain meaningful. Check whether the prior estimate overstated or understated actual growth. Adjust the model when retention, indexing, or business volume changes. Avoid treating a straight line as a permanent property of the database.

The useful result is a defensible request with visible limits. You can explain which history supports the database estimate and which current tables deserve investigation. As table snapshots accumulate, those explanations become stronger. Storage planning becomes a repeatable conversation instead of a larger number copied from last year's spreadsheet.

Related reading on this blog: Why Table Size Numbers Disagree: Reserved, Used and Unused Space and How to Prevent Common SQL Server Performance Problems Efficiently With Smart Capacity Planning.

A growth forecast is not a disk guarantee, it is a plan backed by a stated trend.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.