Logging Free Space per Volume Into a Table Each Day

By the time the disk alert arrives, the capacity trend has turned into a problem. Recording free space per volume gives you a daily history to inspect before capacity gets tight. SQL Server can sample the volumes holding database files, then compare each observation with the previous one.

Use Database Files to Discover Visible Volumes

sys.master_files lists database files known to the instance. sys.dm_os_volume_stats returns volume information for each eligible file's location. CROSS APPLY connects those two sources. Several database files can live on the same volume, so the initial result can repeat the same mount point many times.

I deduplicate by volume before storing capacity history. Summing available_bytes across file rows would count the same free space repeatedly. That produces an impressive number and a very poor storage forecast. Keep one selected observation per volume instead.

The method covers volumes containing database files visible through this metadata. A backup-only drive without a database file does not appear simply because SQL Server uses it for backups. That limitation belongs in the monitoring description. Use an approved Windows volume-inspection method for the remaining drives when the broader capacity review requires them.

Track free space per volume across comparable collection dates and label gaps instead of treating a missed sample as daily growth.

Create a Small History Table for Free Space per Volume

The history table stores an observation date, capture timestamp, mount point, total bytes, and available bytes. The composite key permits one selected daily observation per mount point. The example uses UTC dates consistently; choose a documented business-day rule if your capacity process uses another boundary.

Run the setup in an approved administrative database. The table is a durable operational record, so choose its location and permissions deliberately. Do not create it separately in every application database unless that distributed design is intended.

I keep byte values as bigint and convert them for display later. Storing a rounded free-space percentage loses information needed for change calculations. The mount-point column uses nvarchar(256), the same type the DMV returns, so the composite key stays under the 900-byte clustered key limit. They identify the observed path, while storage remapping can change what that path represents. Record infrastructure changes alongside the history instead of assuming a name always refers to the same physical capacity.

CREATE TABLE dbo.VolumeSpaceHistory
(ObservationDate date NOT NULL,CapturedAt datetime2 NOT NULL,
 VolumeMountPoint nvarchar(256) NOT NULL,TotalBytes bigint NOT NULL,AvailableBytes bigint NOT NULL,
 CONSTRAINT PK_VolumeSpaceHistory PRIMARY KEY(ObservationDate,VolumeMountPoint));

Insert One Reviewed Daily Snapshot

The sampling query numbers file observations within each mount point and selects one. Those volume reads are not an atomic snapshot of every disk at precisely the same instant. The capture timestamp describes the collection, while the available values reflect the calls performed during it.

The NOT EXISTS guard prevents an ordinary rerun from inserting a second daily row. The primary key is the final duplicate protection. If concurrent collectors are possible, give the job one owner or add an approved concurrency strategy and explicit duplicate handling. Do not rely on a plain existence check as a complete race-condition solution.

Put this reviewed INSERT into a SQL Server Agent T-SQL job step pointing at the administrative database. Schedule it once per chosen day and monitor job failures. A missing day should remain a missing observation, not be filled with invented space values. The collector's successful execution and the history row's presence are separate checks.

DECLARE @Day date=CONVERT(date,SYSUTCDATETIME());
WITH Volumes AS
(
 SELECT v.volume_mount_point,v.total_bytes,v.available_bytes,
 ROW_NUMBER() OVER(PARTITION BY v.volume_mount_point ORDER BY f.database_id,f.file_id) AS ChoiceNumber
 FROM sys.master_files AS f
 CROSS APPLY sys.dm_os_volume_stats(f.database_id,f.file_id) AS v
 WHERE v.volume_mount_point IS NOT NULL
)
INSERT dbo.VolumeSpaceHistory
SELECT @Day,SYSUTCDATETIME(),v.volume_mount_point,v.total_bytes,v.available_bytes
FROM Volumes AS v
WHERE v.ChoiceNumber=1 AND NOT EXISTS
(SELECT 1 FROM dbo.VolumeSpaceHistory AS h
 WHERE h.ObservationDate=@Day AND h.VolumeMountPoint=v.volume_mount_point);

Compare Free Space per Volume With the Previous Observation

LAG retrieves the previous recorded available-byte value for each mount point. Subtract it from the current value to see growth or recovery of free space. A negative change means available space declined between those two observations; a positive change means it increased.

The previous observation is not automatically yesterday. Job failures or paused collection can leave gaps. Include the prior date so the report does not label a multiday difference as daily consumption. The next query reports the interval and converts values to display units without overwriting the stored bytes.

Which volumes are losing capacity repeatedly? Compare a sequence of observations rather than ranking one unusual day. A purge, backup cleanup, or file expansion can create a large individual change. Total capacity changes also matter, so retain TotalBytes and investigate a resize separately from ordinary file growth. A free-space percentage can improve because the volume grew, not because the workload used less storage.

WITH Previous AS
(
 SELECT *,LAG(AvailableBytes) OVER(PARTITION BY VolumeMountPoint ORDER BY ObservationDate) AS PreviousAvailable,
 LAG(ObservationDate) OVER(PARTITION BY VolumeMountPoint ORDER BY ObservationDate) AS PreviousDate
 FROM dbo.VolumeSpaceHistory
)
SELECT ObservationDate,PreviousDate,VolumeMountPoint,
 AvailableBytes/1048576.0 AS AvailableMB,
 (AvailableBytes-PreviousAvailable)/1048576.0 AS ChangeMB,
 100.0*AvailableBytes/NULLIF(TotalBytes,0) AS FreePct
FROM Previous ORDER BY VolumeMountPoint,ObservationDate;

Connect the Volume Trend to File Growth

Volume free space describes all consumers of that volume, not just SQL Server data. Compare a decline with database-file growth and other approved storage evidence. The SQL sampler cannot attribute every byte to a database because it observes the shared volume's available capacity.

Review file autogrowth settings and the workloads that expand files. Predictable planned capacity is easier to manage than repeated emergency expansion. At the same time, free space inside a database file is different from free space on its volume. Do not add those two figures into one capacity measure without explaining their relationship.

Keep backup retention and administrative files in the review when they share the volume. A stable data-file size does not prove a stable disk. The history is a signal directing that investigation. It should not automatically trigger shrink operations or deletion of files whose ownership and retention rules have not been reviewed.

Maintain Collection and Capacity Decisions Together

Monitor the Agent job and verify new history rows after schedule changes or restarts. Apply a retention policy that preserves the period needed for capacity planning. A table containing years of identical samples is useful only if someone can still read and maintain the relevant trend.

Alert on collection gaps as well as low space. Missing evidence can otherwise look like a quiet stable period. Keep the limitations visible: database-file volumes only, daily granularity, and no direct attribution of other disk consumers.

Logging free space per volume turns a momentary reading into a reviewable history. Store one observation per volume, preserve the sample interval, and explain large changes with supporting evidence. The useful outcome is enough time to plan capacity deliberately before the next alert becomes an emergency.

Related reading on this blog: Available Free Space in Data and Log File and Monitoring Database Autogrowth Settings.

A free-space reading is not a capacity trend, it is one observation that needs history.

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