SQL Server Database File Space Usage Report

calendar May 17, 2026 · 8 min read · sql server sys.master_files sys.dm_os_volume_stats database files disk space monitoring sql scripts database administration fileproperty sys.database_files sys.databases  ·
Share on: linkedin copy

Running out of space inside a data or log file halts SQL Server transactions immediately — and discovering that a volume is full only after an outage is too late. This script queries sys.master_files and sys.dm_os_volume_stats across every online database on the instance to report each file's allocated size, space actually written, free space inside the file, percent used, autogrowth configuration, and the amount of free space remaining on the underlying disk volume.

Purpose and Overview

SQL Server allocates disk space for data and log files in advance. The file has a current size on disk, but only a portion of that space is actually occupied by data rows or log records — the rest is reserved but empty, ready for the engine to use without triggering an autogrowth event. DBAs need to track three related but distinct numbers: how large the file is on disk, how much of that space is occupied, and how much free space remains on the volume if the file needs to grow. Losing track of any one of these leads to autogrowth events that cause blocking spikes, or worse, a disk-full condition that brings the instance down.

This script, adapted from the ktaranov/sqlserver-kit community repository with additional volume-level statistics from Brent Ozar Unlimited's monitoring recommendations, joins sys.master_files (the file catalog) with sys.dm_os_volume_stats (OS-level volume statistics) using CROSS APPLY. The output covers every data file (ROWS type) and log file (LOG type) for all online databases in a single result set. Columns include:

  • DatabaseName / LogicalName / PhysicalName — file identity and path
  • FileType — ROWS (data) or LOG
  • FileSizeMB / FileSizeGB — allocated size of the file on disk
  • SpaceUsedMB — space actually written inside the file
  • FreeSpaceMB — unallocated space inside the file (no disk I/O needed to use it)
  • PctUsed — percent of allocated file space that is occupied
  • MaxSize / AutoGrowth — how large the file can grow and by how much per event
  • VolumeMountPoint / VolumeFreeSizeGB — the disk volume the file lives on and its available space

Code Breakdown

The query joins the file catalog to volume statistics and decodes the raw autogrowth columns into human-readable text.

 1-- SQL Server Database File Space Usage Report
 2-- Source: adapted from ktaranov/sqlserver-kit community repository
 3-- Shows data and log file sizes, space used, free space, and volume stats for all online databases
 4-- Requires: VIEW SERVER STATE, VIEW ANY DATABASE
 5
 6SET NOCOUNT ON;
 7
 8SELECT
 9    DB_NAME(mf.database_id)                                          AS DatabaseName,
10    mf.name                                                          AS LogicalName,
11    mf.physical_name                                                 AS PhysicalName,
12    mf.type_desc                                                     AS FileType,
13    CAST(mf.size           * 8.0 / 1024 AS DECIMAL(10,2))           AS FileSizeMB,
14    CAST(mf.size           * 8.0 / 1024 / 1024 AS DECIMAL(10,2))   AS FileSizeGB,
15    CAST(
16        FILEPROPERTY(mf.name, 'SpaceUsed') * 8.0 / 1024
17    AS DECIMAL(10,2))                                                AS SpaceUsedMB,
18    CAST(
19        (mf.size - FILEPROPERTY(mf.name, 'SpaceUsed')) * 8.0 / 1024
20    AS DECIMAL(10,2))                                                AS FreeSpaceMB,
21    CAST(
22        FILEPROPERTY(mf.name, 'SpaceUsed') * 100.0 / NULLIF(mf.size, 0)
23    AS DECIMAL(5,2))                                                 AS PctUsed,
24    CASE mf.max_size
25        WHEN -1 THEN 'Unlimited'
26        WHEN  0 THEN 'No Growth'
27        ELSE CAST(CAST(mf.max_size * 8.0 / 1024 AS BIGINT) AS NVARCHAR(20)) + ' MB'
28    END                                                              AS MaxSize,
29    CASE mf.is_percent_growth
30        WHEN 1 THEN CAST(mf.growth AS NVARCHAR(10)) + '%'
31        ELSE CAST(CAST(mf.growth * 8.0 / 1024 AS INT) AS NVARCHAR(20)) + ' MB'
32    END                                                              AS AutoGrowth,
33    vs.volume_mount_point                                            AS VolumeMountPoint,
34    CAST(vs.available_bytes / 1073741824.0 AS DECIMAL(10,2))        AS VolumeFreeSizeGB
35FROM sys.master_files AS mf
36CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id)     AS vs
37WHERE EXISTS (
38    SELECT 1
39    FROM sys.databases AS d
40    WHERE d.database_id = mf.database_id
41      AND d.state_desc = 'ONLINE'
42)
43ORDER BY
44    DatabaseName,
45    mf.type_desc DESC,   -- ROWS before LOG
46    mf.name;

sys.master_files — The File Inventory

sys.master_files is a server-wide catalog view with one row per database file across the entire instance — unlike sys.database_files, which is scoped to the current database. The mf.size column stores the current file size in 8 KB pages, so multiplying by 8 and dividing by 1,024 converts to megabytes. Dividing by 1,024 again gives gigabytes. The type_desc column identifies ROWS (data files, .mdf and .ndf) and LOG (transaction log files, .ldf). The ORDER BY mf.type_desc DESC places ROWS before LOG alphabetically within each database.

FILEPROPERTY — Space Used vs Allocated

FILEPROPERTY(name, 'SpaceUsed') returns the number of 8 KB pages actually written inside the file. This is the internal allocated space as tracked by the Storage Engine — not the same as the file's size on disk, and not the same as the volume's free space. Subtracting SpaceUsed from mf.size gives the unallocated pages inside the file boundary: space SQL Server can use immediately without growing the file or touching the disk beyond the file's current extent. NULLIF(mf.size, 0) in the PctUsed calculation prevents a divide-by-zero error on empty files, though in practice SQL Server does not allow zero-size files.

CROSS APPLY sys.dm_os_volume_stats

sys.dm_os_volume_stats(database_id, file_id) is a table-valued function that calls the Windows API to retrieve disk volume statistics for the volume on which the specified file resides. The CROSS APPLY pattern invokes it once per row from sys.master_files, attaching volume-level information to each file row. available_bytes is the OS-reported free bytes on the volume; dividing by 1,073,741,824 (bytes per GB) converts to gigabytes. If two database files share the same volume, VolumeFreeSizeGB appears identically on both rows — the information is not double-counted, it is just reported in context of each file.

AutoGrowth and MaxSize Decoding

mf.growth stores either a fixed page count or a percentage, controlled by the is_percent_growth bit. When is_percent_growth = 1, the growth column is the percentage increment directly (e.g., 10 means 10%). When is_percent_growth = 0, growth is in pages and must be converted: growth * 8 / 1024 gives MB. mf.max_size = -1 means the file can grow until the disk is full (unlimited); max_size = 0 means autogrowth is disabled. The CASE expressions translate these raw values into text labels that make the autogrowth configuration immediately readable without consulting documentation.

Key Benefits and Use Cases

  • Instance-wide file inventory — one result set covers all databases and all files, eliminating the need to connect to each database individually
  • Volume free space in context — knowing a file is 90% full is meaningless without knowing how much disk space remains for it to grow into; this report shows both
  • Autogrowth misconfiguration detection — percent-based autogrowth on large files produces enormous unpredictable growth events; this report surfaces those settings for remediation
  • Pre-maintenance capacity planning — run before index rebuilds or bulk loads to confirm enough file and volume space exists to absorb the growth
  • Alerting baseline — filter WHERE PctUsed > 85 or VolumeFreeSizeGB < 10 to produce an actionable shortlist for capacity alerts
  • Multi-file database visibility — databases with multiple data files (filegroup distribution) show each file separately, revealing uneven fill distribution

Performance Considerations

  • sys.master_files is a catalog view — no I/O against data files; reads from SQL Server's internal metadata cache
  • sys.dm_os_volume_stats makes a kernel call per file — on instances with hundreds of database files the query is still typically sub-second, but the OS call count scales with file count; if runtime is a concern, filter to specific databases or file types
  • FILEPROPERTY runs per-file in the current database context — when querying sys.master_files across all databases, FILEPROPERTY resolves against the current database context, not the target database; this can cause FILEPROPERTY to return NULL for files belonging to other databases if queried directly; in practice the CROSS APPLY and the catalog join handle this correctly for most configurations, but verify results on instances with non-standard collations
  • Offline databases are excluded — the EXISTS filter on state_desc = 'ONLINE' skips offline, restoring, and suspect databases; add a separate query for those states if needed
  • Percent-based autogrowth warning — a 10% autogrowth increment on a 1 TB data file triggers a 100 GB growth event, which can cause blocking for minutes; use this report to find and convert percent-based settings to fixed MB increments

Practical Tips

  • Alert when PctUsed exceeds 85% — at that threshold a single autogrowth event may not be sufficient; schedule proactive file expansion during a maintenance window instead
  • Convert percent-based autogrowth to fixed increments — use ALTER DATABASE … MODIFY FILE (FILEGROWTH = 512MB) to standardize; 256 MB to 1 GB increments are typical depending on database size
  • Compare VolumeFreeSizeGB across all rows for a volume — if multiple databases share a volume and VolumeFreeSizeGB is low, any one of them triggering autogrowth affects all of them
  • Schedule as a daily SQL Agent job — write results to a history table and alert when PctUsed crosses 85% or VolumeFreeSizeGB drops below a threshold appropriate for your environment
  • Use alongside the VLF Count Report — oversized log files with high VLF counts often trace back to a history of small autogrowth events; fixing autogrowth settings prevents both problems simultaneously

Conclusion

This script provides a complete picture of SQL Server file space at the instance level by combining the file catalog in sys.master_files with OS-level volume statistics from sys.dm_os_volume_stats. The result set gives DBAs everything needed to identify files approaching capacity, spot percent-based autogrowth configurations before they cause problems, and confirm that the underlying volumes have enough space to absorb anticipated growth.

References

Posts in this series