Forecasting the Disk-Full Date From a Daily File Size History

A disk-full date is an estimate built from history, and today’s free-space number cannot give you one. You need a daily record of file sizes and free space. Then a little arithmetic tells you roughly how many days are left.

A free-space number is only a snapshot

Your manager walks over and asks, “The data drive has 150 GB free. How long until it is full?” You can answer with a shrug or with a date. The shrug is easier, but the date is what keeps you out of a 2 AM page.

Free space today says nothing about speed. Plenty of free space is a week of runway if the files grow fast. To know the speed, you have to write the numbers down every day.

Collect one row per file per day

The collector below keeps one row per database file. It stores the date, the allocated size, the used size, and the free bytes on the volume that holds the file. Allocated and used answer different questions: the first is how big the file is, the second is how much of it holds data.

FILEPROPERTY works in the context of the current database. So run this in the database you are measuring, and repeat it for each one. My demo keeps the history in a temp table. In real life you would insert into a permanent table once a day from an Agent job.

DROP TABLE IF EXISTS #FileSizeHistory;

CREATE TABLE #FileSizeHistory
(
    SnapshotDate date NOT NULL,
    FileName sysname NOT NULL,
    VolumeMountPoint nvarchar(256) NULL,
    AllocatedBytes bigint NOT NULL,
    UsedBytes bigint NULL,
    VolumeFreeBytes bigint NULL
);

INSERT #FileSizeHistory
SELECT CONVERT(date, SYSDATETIME()), df.name, vs.volume_mount_point,
       CONVERT(bigint, df.size) * 8192,
       CONVERT(bigint, FILEPROPERTY(df.name, 'SpaceUsed')) * 8192,
       vs.available_bytes
FROM sys.database_files AS df
CROSS APPLY sys.dm_os_volume_stats(DB_ID(), df.file_id) AS vs;

SELECT FileName, AllocatedBytes, UsedBytes, VolumeFreeBytes
FROM #FileSizeHistory
ORDER BY FileName;

You get two rows, one for the data file and one for the log. Your sizes will differ from mine. Look at the last column. Both rows carry the same free-space value, because that number belongs to the volume, not to the file.

Do not multiply the free space

That repeated number is a trap. If you sum free space across files, a volume with several files is counted several times. The query below shows both answers. WrongFreeSum adds the free bytes per file. VolumeFreeBytes takes the value once.

SELECT VolumeMountPoint,
       COUNT(*) AS FileCount,
       SUM(AllocatedBytes) AS AllocatedOnVolume,
       SUM(VolumeFreeBytes) AS WrongFreeSum,
       MIN(VolumeFreeBytes) AS VolumeFreeBytes
FROM #FileSizeHistory
GROUP BY VolumeMountPoint;

If both files sit on one volume, as they do on my server, WrongFreeSum is exactly double the real value. Add up the allocations, but keep the free space once.

Do the forecast arithmetic

A single day cannot make a trend, so I use a fixed four-day history instead. Allocation grows by 1,073,741,824 bytes, one GiB, every day. Free space starts at 13 GiB and falls by the same amount.

The query divides each increase by the real gap in days, so a missed collection does not fake a faster trend. The mean of three comparable increments is one GiB per day. With 10 GiB free on the last day, that leaves 10 days.

The date is a linear scenario, not a promise. The CASE returns a date only when growth is positive. Flat or shrinking history leaves the date empty instead of promising infinite space.

DECLARE @history TABLE
(
    SnapshotDate date PRIMARY KEY,
    AllocatedBytes bigint,
    FreeBytes bigint
);

INSERT @history VALUES
('2025-01-01', 10737418240, 13958643712),
('2025-01-02', 11811160064, 12884901888),
('2025-01-03', 12884901888, 11811160064),
('2025-01-04', 13958643712, 10737418240);

WITH P AS
(
    SELECT *, LAG(SnapshotDate) OVER (ORDER BY SnapshotDate) AS PreviousDate,
              LAG(AllocatedBytes) OVER (ORDER BY SnapshotDate) AS PreviousBytes
    FROM @history
),
D AS
(
    SELECT *, CONVERT(decimal(28,4), AllocatedBytes - PreviousBytes)
              / NULLIF(DATEDIFF(day, PreviousDate, SnapshotDate), 0) AS GrowthBytesPerDay
    FROM P
),
T AS
(
    SELECT AVG(GrowthBytesPerDay) AS MeanDailyGrowth,
           COUNT(GrowthBytesPerDay) AS ComparableSamples
    FROM D
)
SELECT d.FreeBytes, t.MeanDailyGrowth, t.ComparableSamples,
       d.FreeBytes / NULLIF(t.MeanDailyGrowth, 0) AS EstimatedDaysLeft,
       CASE WHEN t.MeanDailyGrowth > 0
            THEN DATEADD(day, CONVERT(int, FLOOR(d.FreeBytes / t.MeanDailyGrowth)), d.SnapshotDate)
       END AS EstimatedFullDate
FROM D AS d
CROSS JOIN T AS t
WHERE d.SnapshotDate = (SELECT MAX(SnapshotDate) FROM @history);

Know when the trend lies

Averages break at discontinuities. A shrink, a file move, a recreated file, or a one-time pre-grow all change the story. When the history no longer describes the same files, start a new baseline instead of averaging across the gap.

Also label your policy. An average of all changes and an average of growth-only days answer different questions. Print the sample count and the observation date next to the forecast.

Finally, keep a plain free-space alert beside the trend. A big import or another application can eat the rest of the drive long before your date arrives. The trend is for planning. The alert is for the surprise.

The last block removes the temp table.

DROP TABLE IF EXISTS #FileSizeHistory;

Next time someone asks how long you have, you can give a date and its limits.

A disk forecast is not a promise, it is a trend with an expiry date.

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