Keeping track of database file sizes and available free space is a basic but essential part of SQL Server administration.
If you don’t know how large your data and log files are, or how much free space they contain, capacity planning becomes guesswork. Problems tend to surface late, usually during growth events, backups, or unexpected workload spikes.
Why It Matters
Database files grow over time, but rarely in a smooth or predictable way.
In day-to-day DBA work, file size and free space checks are often needed when:
- Planning disk capacity and future growth
- Investigating sudden storage alerts
- Reviewing autogrowth behaviour
- Preparing for server builds or migrations
- Understanding how recovery model affects log usage
Free space inside a database file is just as important as the size of the file itself. A file can be large but mostly empty, or relatively small and nearly full. Both situations require different decisions.
If you also need to understand where database files live on disk, see Get Database File Names and Paths in SQL Server.
The Script
This script loops through all databases on the instance and returns file sizes and free space for each file. It uses sys.database_files and FILEPROPERTY to calculate space used and free space within each file.
DROP TABLE IF EXISTS #db_file_sizes
CREATE TABLE #db_file_sizes (
database_name NVARCHAR(128) NOT NULL,
file_name NVARCHAR(128) NOT NULL,
file_type NVARCHAR(60) NOT NULL,
file_path NVARCHAR(260) NOT NULL,
size_mb DECIMAL(12,2) NOT NULL,
used_mb DECIMAL(12,2) NOT NULL,
free_mb DECIMAL(12,2) NOT NULL,
pct_free DECIMAL(5,1) NOT NULL,
collection_date DATE NOT NULL
)
DECLARE @db_name NVARCHAR(128)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE'
-- AND database_id > 4 -- uncomment to exclude system databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql NVARCHAR(MAX) =
N'USE ' + QUOTENAME(@db_name) + N';
INSERT INTO #db_file_sizes
SELECT
DB_NAME() AS database_name,
name AS file_name,
type_desc AS file_type,
physical_name AS file_path,
CAST(size * 8.0 / 1024 AS DECIMAL(12,2)) AS size_mb,
CAST(FILEPROPERTY(name, ''SpaceUsed'') * 8.0 / 1024 AS DECIMAL(12,2)) AS used_mb,
CAST((size - FILEPROPERTY(name, ''SpaceUsed'')) * 8.0 / 1024 AS DECIMAL(12,2)) AS free_mb,
CAST(100.0 * (size - FILEPROPERTY(name, ''SpaceUsed''))
/ NULLIF(size, 0) AS DECIMAL(5,1)) AS pct_free,
CAST(GETDATE() AS DATE) AS collection_date
FROM sys.database_files;'
EXEC sp_executesql @sql
FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT *
FROM #db_file_sizes
ORDER BY pct_free ASC, size_mb DESC -- lowest free space first
DROP TABLE #db_file_sizes
Reading The Output
The result is sorted by pct_free ascending — files with the least free space appear first, which is the most useful order for a monitoring view.
| Column | What It Means |
|---|---|
database_name | The database the file belongs to |
file_name | The logical file name as registered in SQL Server |
file_type | ROWS (data file) or LOG (transaction log file) |
file_path | Full physical path on disk — useful for confirming files are on the right drive |
size_mb | Total allocated file size. This is what the file occupies on disk |
used_mb | Space actually consumed by data or log records within the file |
free_mb | Unallocated space inside the file. SQL Server can use this without extending the file on disk |
pct_free | Free space as a percentage of total file size — the primary column to watch |
collection_date | Date the snapshot was taken |
Log file free space varies significantly depending on recovery model and backup frequency. Data files tend to show more predictable patterns.
If you see files frequently approaching full capacity, that’s a sign to review growth settings or disk allocation before autogrowth becomes a recurring problem.
To understand when files are growing rather than just their current size, see Show Database Growth Events in SQL Server.
Production Notes
- This reports free space inside the database file, not free space on disk. A file with 50% internal free space still occupies 100% of its allocated size on the volume. Check disk-level free space separately.
- The script only runs against
ONLINEdatabases. Offline, restoring, or suspect databases are skipped to avoid errors. - Shrinking files to reclaim free space is usually a last resort, especially in production. It causes severe index fragmentation and the space fills back up again quickly.
- Log files with low
pct_freearen’t always a problem. A log file at 5% free running hourly log backups is fine. The same log file withlog_reuse_wait_desc = LOG_BACKUPmeans log backups aren’t running and it will keep growing. Pair with log usage checks to confirm. See How to Check Transaction Log Space Usage in SQL Server. - Consistent file layouts and sensible fixed-size growth increments reduce the need for reactive changes.
Related
- Get Database File Names and Paths in SQL Server — physical file paths and drive locations for every database file
- Show Database Growth Events in SQL Server — when and how much files have been growing
- How to Check Transaction Log Space Usage in SQL Server — log-specific space and recovery model context
- sys.database_files — Microsoft Docs
- FILEPROPERTY — Microsoft Docs
This is not a script you run once and forget. Keep it handy, or schedule it, so you can make decisions while you still have options rather than reacting under pressure.
Leave a Reply