Free space on every drive that holds a database file, and each file’s size, space used and growth setting. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025.

-- 04_Disks_and_Files.sql - free space on each drive, and every database file's size, use and growth. Read-only.
SET NOCOUNT ON;
SELECT DISTINCT vs.volume_mount_point AS drive, vs.logical_volume_name,
       CONVERT(decimal(10,1), vs.total_bytes / 1073741824.0) AS size_gb,
       CONVERT(decimal(10,1), vs.available_bytes / 1073741824.0) AS free_gb,
       CONVERT(decimal(5,1), vs.available_bytes * 100.0 / vs.total_bytes) AS free_pct
FROM sys.master_files AS mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
ORDER BY free_pct;

SELECT DB_NAME(mf.database_id) AS [database], mf.name AS logical_name, mf.type_desc, mf.physical_name,
       CONVERT(decimal(12,1), mf.size / 128.0) AS size_mb,
       CASE WHEN mf.is_percent_growth = 1 THEN CONVERT(varchar(10), mf.growth) + '%'
            ELSE CONVERT(varchar(20), mf.growth / 128) + ' MB' END AS growth,
       CASE WHEN mf.max_size = -1 THEN 'unlimited' ELSE CONVERT(varchar(20), CONVERT(bigint, mf.max_size) / 128) + ' MB' END AS max_size,
       CONVERT(decimal(10,1), fs.io_stall_read_ms * 1.0 / NULLIF(fs.num_of_reads, 0)) AS avg_read_ms,
       CONVERT(decimal(10,1), fs.io_stall_write_ms * 1.0 / NULLIF(fs.num_of_writes, 0)) AS avg_write_ms
FROM sys.master_files AS mf
LEFT JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON fs.database_id = mf.database_id AND fs.file_id = mf.file_id
ORDER BY [database], mf.type, mf.file_id;
-- avg_read_ms / avg_write_ms since start-up: under 20 ms is good; over 50 ms the storage is struggling.