Free Space in Database Files: Check Data and Log Files

To find the free space in database files, subtract the pages in use from the pages each file owns. One query does that for every file in the current database.

Space Inside a File Is Not Space on the Drive

A database file is like a trunk with a fixed size. The trunk can be nearly full or half empty, and that tells you nothing about the room in the attic. Two numbers matter. The first is the free space inside each file. The second is the free space on the drive that holds it.

The free space inside a file decides when the file must grow. A growth step takes time, and a full drive stops it. Both numbers are measured below. The demo creates a database named FileSpaceDemo with a 64 MB data file and a 64 MB log file. The script sets both sizes with MODIFY FILE, so use a larger size when your model database is larger. It loads the rows with GENERATE_SERIES, which needs SQL Server 2022 and compatibility level 160.

IF DB_ID(N'FileSpaceDemo') IS NULL CREATE DATABASE FileSpaceDemo;
GO
ALTER DATABASE FileSpaceDemo SET RECOVERY SIMPLE;
ALTER DATABASE FileSpaceDemo MODIFY FILE (NAME = N'FileSpaceDemo', SIZE = 64MB, FILEGROWTH = 16MB);
ALTER DATABASE FileSpaceDemo MODIFY FILE (NAME = N'FileSpaceDemo_log', SIZE = 64MB, FILEGROWTH = 16MB);
GO
USE FileSpaceDemo;
GO
DROP TABLE IF EXISTS dbo.Notes;
CREATE TABLE dbo.Notes (NoteID int IDENTITY(1,1) PRIMARY KEY, Body char(1000) NOT NULL);
INSERT INTO dbo.Notes (Body) SELECT REPLICATE('x', 1000) FROM GENERATE_SERIES(1, 20000);

The insert writes about 26 MB of rows. The log file also records the work, and it grows past its starting size. The next query shows what each file looks like now.

The Query for Free Space in Each File

The query for the free space in database files starts from sys.database_files. That view has a row for every file of the current database. The size column counts 8 KB pages, so dividing by 128 gives megabytes. The FILEPROPERTY function with SpaceUsed returns the pages a file uses. The CROSS APPLY reads it once for each file, so the formulas stay short.

SELECT f.name AS FileName, f.type_desc AS FileType,
       CAST(f.size / 128.0 AS decimal(12,2)) AS SizeMB,
       CAST(u.UsedPages / 128.0 AS decimal(12,2)) AS UsedMB,
       CAST((f.size - u.UsedPages) / 128.0 AS decimal(12,2)) AS FreeMB,
       CAST(100.0 * (f.size - u.UsedPages) / f.size AS decimal(5,1)) AS FreePercent
FROM sys.database_files AS f
CROSS APPLY (VALUES (CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int))) AS u(UsedPages)
ORDER BY f.type, f.file_id;

The data file holds 26.31 MB of its 64 MB, so 37.69 MB are free. The log file grew from 64 MB to 96 MB while the rows loaded. It uses only 3.18 MB of that now. Your numbers will differ a little. Read the pattern. A file with a high free percentage has room, and a file near zero is about to grow.

A file with a growth limit can still run out. Check max_size in sys.database_files, where -1 means no limit. A memory-optimized or FILESTREAM container has no pages. It shows size 0 and NULL for free space here.

Some scripts write [Alias] = column, and others write column AS alias. Both forms are valid T-SQL. The AS form is standard SQL, so it is the one used here.

Check the Log With a Second Source

Do not trust a single number for the log. The view sys.dm_db_log_space_usage reports the log use from a different source. If both sources agree, the query is right.

SELECT CAST(used_log_space_in_bytes / 1048576.0 AS decimal(12,2)) AS UsedLogMB,
       CAST(used_log_space_in_percent AS decimal(5,1)) AS UsedLogPercent
FROM sys.dm_db_log_space_usage;

The log shows 3.18 MB used, the same as the first query. FILEPROPERTY works for the log file here. On SQL Server 2025 the two sources agree. Prefer the log view when you need the percentage that the log reports.

The Drive Matters Too

Free space inside a file only helps until the file must grow. After that, the drive decides. The function sys.dm_os_volume_stats returns the size and free space of the volume that holds each file.

SELECT f.name AS FileName, v.volume_mount_point AS Drive,
       CAST(v.total_bytes / 1073741824.0 AS decimal(9,1)) AS DriveGB,
       CAST(v.available_bytes / 1073741824.0 AS decimal(9,1)) AS DriveFreeGB
FROM sys.database_files AS f
CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.file_id) AS v
ORDER BY f.file_id;

Both files sit on the C drive of the test server, so the query returns the same drive twice. On a server with separate drives, the log and data rows show different volumes. Compare DriveFreeGB with the growth setting of the file. A file that grows by 16 MB needs 16 MB of free space on its drive at that moment.

Run It for Every Database

FILEPROPERTY works in the current database only. To check all databases, run the same query in each one. The script below loops over the online databases and collects one row per file in a temporary table. It only reads.

SET NOCOUNT ON;
CREATE TABLE #Space (DatabaseName sysname, FileName sysname, SizeMB decimal(12,2), FreeMB decimal(12,2));
DECLARE @db sysname, @proc nvarchar(300);
DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.databases WHERE state = 0 ORDER BY name;
OPEN dbs;
FETCH NEXT FROM dbs INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @proc = QUOTENAME(@db) + N'.sys.sp_executesql';
    BEGIN TRY
        INSERT #Space
        EXEC @proc N'SELECT DB_NAME(), name, size / 128.0, (size - FILEPROPERTY(name, ''SpaceUsed'')) / 128.0 FROM sys.database_files';
    END TRY
    BEGIN CATCH
        PRINT CONCAT(N'Skipped ', @db, N': ', ERROR_MESSAGE());
    END CATCH;
    FETCH NEXT FROM dbs INTO @db;
END;
CLOSE dbs;
DEALLOCATE dbs;
SELECT COUNT(DISTINCT DatabaseName) AS DatabasesRead, COUNT(*) AS FilesRead FROM #Space;
SELECT TOP (3) DatabaseName, FileName, FreeMB FROM #Space WHERE FreeMB IS NOT NULL ORDER BY FreeMB;
DROP TABLE #Space;

The script returns one row per file. The two summary queries at the end count the databases it read. They also list the three files with the least free space. A database that is dropped while the loop runs lands in the CATCH block and is skipped.

What to Remember

Compare the file size with the used pages, not with the drive. Watch the free percentage of each file, and check the drive when a file is close to its limit. A log file that shows a high used percentage deserves a look at its recovery model and its backups. One query reads the free space in database files, so there is no need to open a dialog.

You could argue that a monitoring tool does this for you. It does, and it reads the same views. Knowing the query lets you check a single server in seconds, and it shows what the tool reports.

When you finish, drop the demo database.

USE master;
GO
ALTER DATABASE FileSpaceDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE FileSpaceDemo;

Free space is not what the drive says, it is what each file has left.

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