Allocated vs Used, Split by Data and Log, Every Database, One Pass
Database Files Detail gives you file-level detail. This script gives you the consolidated view above that: allocated, used, and free space for every online database, split into data and log separately, with human-readable units (MB/GB/TB) and raw numbers for charting, all in one pass.
The split matters because data and log space behave completely differently: data space fills gradually as rows are added, log space fills based on transaction activity and recovery model, and a database can be nearly out of one while having plenty of the other.
Database Sizes and Free Space answers a related but different question, biggest databases first. This script orders by tightest free space first, for triage rather than inventory.
Why Database Free Space Summary Matters
- Free space at the database level (not just the drive level) is what actually determines whether an insert or a log write can complete, a drive with plenty of free space doesn’t help if the database’s own files are capped
- Separating data free space from log free space stops one from hiding a problem in the other, a database can look “healthy” on total free space while its log is nearly full
- Free % is the number that scales, comparing raw MB across databases of wildly different sizes doesn’t tell you which one needs attention first, free % does
- This reads system metadata and one
DBCC SQLPERF(LOGSPACE)call, not a per-database scan, safe to run on a busy production instance
When to Run This Script
- Routine capacity and storage reviews, across every database in one pass
- Investigating disk space pressure, to see which specific database and which specific file type (data or log) is actually consuming it
- Before a maintenance window that might grow files, to confirm the starting baseline
- Alongside Database Growth Risk and Forecast when planning ahead rather than just checking the current state
The Script
✓ Verified
- Tested on: SQL Server 2025 (RTM CU5), Windows lab instance
- Last verified: 2026-08-13 (saved output from a real run, Get-DatabaseFreeSpaceSummary-20260813-184504.csv)
- Permissions: VIEW SERVER STATE, VIEW DATABASE STATE
- Safety: read-only, impact low
/*
Script Name : Get-DatabaseFreeSpaceSummary
Category : monitoring
Purpose : Allocated, used, and free space for all online databases, ordered by total free space descending.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-database-free-space-summary/)
Requires : VIEW SERVER STATE, VIEW DATABASE STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
/* Set to 1 to hide master/model/msdb/tempdb */
DECLARE @ExcludeSystemDBs BIT = 0;
/* ── Temp tables ─────────────────────────────────────────────────────────── */
IF OBJECT_ID('tempdb..#SpaceInfo') IS NOT NULL DROP TABLE #SpaceInfo;
CREATE TABLE #SpaceInfo (
DatabaseName NVARCHAR(128) NOT NULL,
DataAllocMB DECIMAL(20,2) NOT NULL DEFAULT 0,
DataUsedMB DECIMAL(20,2) NOT NULL DEFAULT 0,
LogAllocMB DECIMAL(20,2) NOT NULL DEFAULT 0,
LogUsedMB DECIMAL(20,2) NOT NULL DEFAULT 0
);
IF OBJECT_ID('tempdb..#LogSpace') IS NOT NULL DROP TABLE #LogSpace;
CREATE TABLE #LogSpace (
DatabaseName NVARCHAR(128),
LogSizeMB FLOAT,
LogUsedPct FLOAT,
[Status] TINYINT
);
/* ── Capture log space from DBCC SQLPERF (one call for all DBs) ─────────── */
INSERT INTO #LogSpace
EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS');
/* ── Cursor: collect data-file sizes via per-database context switch ──────── */
DECLARE @db NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);
DECLARE db_cur CURSOR FAST_FORWARD FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND HAS_DBACCESS(name) = 1
AND (@ExcludeSystemDBs = 0 OR database_id > 4)
ORDER BY name;
OPEN db_cur;
FETCH NEXT FROM db_cur INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'
USE ' + QUOTENAME(@db) + N';
INSERT INTO #SpaceInfo (DatabaseName, DataAllocMB, DataUsedMB, LogAllocMB, LogUsedMB)
SELECT
DB_NAME(),
SUM(CASE WHEN type_desc = ''ROWS''
THEN CAST(size AS BIGINT) * 8.0 / 1024
ELSE 0 END),
SUM(CASE WHEN type_desc = ''ROWS''
THEN CAST(COALESCE(FILEPROPERTY(name, ''SpaceUsed''), 0) AS BIGINT) * 8.0 / 1024
ELSE 0 END),
SUM(CASE WHEN type_desc = ''LOG''
THEN CAST(size AS BIGINT) * 8.0 / 1024
ELSE 0 END),
0
FROM sys.database_files;
';
BEGIN TRY
EXEC sp_executesql @sql;
END TRY
BEGIN CATCH
/* Skip databases that are inaccessible mid-run */
END CATCH;
FETCH NEXT FROM db_cur INTO @db;
END;
CLOSE db_cur;
DEALLOCATE db_cur;
/* ── Merge log-used percentages from DBCC SQLPERF ───────────────────────── */
UPDATE s
SET s.LogUsedMB = CAST(l.LogSizeMB * l.LogUsedPct / 100.0 AS DECIMAL(20,2))
FROM #SpaceInfo s
JOIN #LogSpace l ON l.DatabaseName = s.DatabaseName;
/* ── Final report ────────────────────────────────────────────────────────── */
;WITH calc AS (
SELECT
DatabaseName,
DataAllocMB,
DataUsedMB,
CASE WHEN DataAllocMB - DataUsedMB > 0
THEN DataAllocMB - DataUsedMB ELSE 0 END AS DataFreeMB,
LogAllocMB,
LogUsedMB,
CASE WHEN LogAllocMB - LogUsedMB > 0
THEN LogAllocMB - LogUsedMB ELSE 0 END AS LogFreeMB,
DataAllocMB + LogAllocMB AS TotalAllocMB,
DataUsedMB + LogUsedMB AS TotalUsedMB,
CASE WHEN DataAllocMB - DataUsedMB > 0 THEN DataAllocMB - DataUsedMB ELSE 0 END
+ CASE WHEN LogAllocMB - LogUsedMB > 0 THEN LogAllocMB - LogUsedMB ELSE 0 END AS TotalFreeMB
FROM #SpaceInfo
)
SELECT
DatabaseName,
/* ── Totals ──────────────────────────────────────────────────────────── */
CASE WHEN TotalAllocMB >= 1048576 THEN CAST(CAST(TotalAllocMB / 1048576.0 AS DECIMAL(10,2)) AS VARCHAR) + ' TB'
WHEN TotalAllocMB >= 1024 THEN CAST(CAST(TotalAllocMB / 1024.0 AS DECIMAL(10,2)) AS VARCHAR) + ' GB'
ELSE CAST(CAST(TotalAllocMB AS DECIMAL(10,2)) AS VARCHAR) + ' MB'
END AS [Total Allocated],
CASE WHEN TotalUsedMB >= 1048576 THEN CAST(CAST(TotalUsedMB / 1048576.0 AS DECIMAL(10,2)) AS VARCHAR) + ' TB'
WHEN TotalUsedMB >= 1024 THEN CAST(CAST(TotalUsedMB / 1024.0 AS DECIMAL(10,2)) AS VARCHAR) + ' GB'
ELSE CAST(CAST(TotalUsedMB AS DECIMAL(10,2)) AS VARCHAR) + ' MB'
END AS [Total Used],
CASE WHEN TotalFreeMB >= 1048576 THEN CAST(CAST(TotalFreeMB / 1048576.0 AS DECIMAL(10,2)) AS VARCHAR) + ' TB'
WHEN TotalFreeMB >= 1024 THEN CAST(CAST(TotalFreeMB / 1024.0 AS DECIMAL(10,2)) AS VARCHAR) + ' GB'
ELSE CAST(CAST(TotalFreeMB AS DECIMAL(10,2)) AS VARCHAR) + ' MB'
END AS [Total Free],
CAST(
CASE WHEN TotalAllocMB > 0
THEN TotalFreeMB * 100.0 / TotalAllocMB
ELSE 0
END AS DECIMAL(5,2)) AS [Free %],
/* ── Data files ──────────────────────────────────────────────────────── */
CASE WHEN DataAllocMB >= 1048576 THEN CAST(CAST(DataAllocMB / 1048576.0 AS DECIMAL(10,2)) AS VARCHAR) + ' TB'
WHEN DataAllocMB >= 1024 THEN CAST(CAST(DataAllocMB / 1024.0 AS DECIMAL(10,2)) AS VARCHAR) + ' GB'
ELSE CAST(CAST(DataAllocMB AS DECIMAL(10,2)) AS VARCHAR) + ' MB'
END AS [Data Allocated],
CASE WHEN DataUsedMB >= 1048576 THEN CAST(CAST(DataUsedMB / 1048576.0 AS DECIMAL(10,2)) AS VARCHAR) + ' TB'
WHEN DataUsedMB >= 1024 THEN CAST(CAST(DataUsedMB / 1024.0 AS DECIMAL(10,2)) AS VARCHAR) + ' GB'
ELSE CAST(CAST(DataUsedMB AS DECIMAL(10,2)) AS VARCHAR) + ' MB'
END AS [Data Used],
CASE WHEN DataFreeMB >= 1048576 THEN CAST(CAST(DataFreeMB / 1048576.0 AS DECIMAL(10,2)) AS VARCHAR) + ' TB'
WHEN DataFreeMB >= 1024 THEN CAST(CAST(DataFreeMB / 1024.0 AS DECIMAL(10,2)) AS VARCHAR) + ' GB'
ELSE CAST(CAST(DataFreeMB AS DECIMAL(10,2)) AS VARCHAR) + ' MB'
END AS [Data Free],
/* ── Log files ───────────────────────────────────────────────────────── */
CASE WHEN LogAllocMB >= 1048576 THEN CAST(CAST(LogAllocMB / 1048576.0 AS DECIMAL(10,2)) AS VARCHAR) + ' TB'
WHEN LogAllocMB >= 1024 THEN CAST(CAST(LogAllocMB / 1024.0 AS DECIMAL(10,2)) AS VARCHAR) + ' GB'
ELSE CAST(CAST(LogAllocMB AS DECIMAL(10,2)) AS VARCHAR) + ' MB'
END AS [Log Allocated],
CASE WHEN LogUsedMB >= 1048576 THEN CAST(CAST(LogUsedMB / 1048576.0 AS DECIMAL(10,2)) AS VARCHAR) + ' TB'
WHEN LogUsedMB >= 1024 THEN CAST(CAST(LogUsedMB / 1024.0 AS DECIMAL(10,2)) AS VARCHAR) + ' GB'
ELSE CAST(CAST(LogUsedMB AS DECIMAL(10,2)) AS VARCHAR) + ' MB'
END AS [Log Used],
CASE WHEN LogFreeMB >= 1048576 THEN CAST(CAST(LogFreeMB / 1048576.0 AS DECIMAL(10,2)) AS VARCHAR) + ' TB'
WHEN LogFreeMB >= 1024 THEN CAST(CAST(LogFreeMB / 1024.0 AS DECIMAL(10,2)) AS VARCHAR) + ' GB'
ELSE CAST(CAST(LogFreeMB AS DECIMAL(10,2)) AS VARCHAR) + ' MB'
END AS [Log Free],
/* ── Raw MB columns for charting ─────────────────────────────────────── */
TotalAllocMB,
TotalUsedMB,
TotalFreeMB
FROM calc
ORDER BY TotalFreeMB DESC;
/* ── Cleanup ─────────────────────────────────────────────────────────────── */
IF OBJECT_ID('tempdb..#SpaceInfo') IS NOT NULL DROP TABLE #SpaceInfo;
IF OBJECT_ID('tempdb..#LogSpace') IS NOT NULL DROP TABLE #LogSpace;How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Allocated, used, and free space for every database, split by data and log:
.\run.ps1 Get-DatabaseFreeSpaceSummary
# To run against a remote sql server:
.\run.ps1 Get-DatabaseFreeSpaceSummary -ServerInstance SQLSERVER01
This script lives in the repo at:
Example Output
Output (24 databases, condensed here):
WatchtowerMetrics is a real, useful contrast here: only 51.31 MB of data free space (nearly full data files) but 1.29 GB of log free space, exactly the kind of split-picture finding a single “total free space” number would hide.
Understanding the Results
- Data Free vs Log Free showing very different pictures — this is the entire reason to separate them; a database with almost no data free space but plenty of log free space needs a data file grow or cleanup, not a log action, and vice versa
- Free % low on a small database — a database with 950 MB total and 159 MB free (16.77%) needs attention sooner than a much larger database at the same percentage, in absolute terms it’s closer to actually running out
- TotalAllocMB/TotalUsedMB/TotalFreeMB raw columns — included specifically for charting or trend tracking over time, alongside the human-readable columns
- A database missing from the results — check
HAS_DBACCESSandstate_desc = 'ONLINE', this script silently skips databases that fail the per-database context switch rather than erroring the whole run
Best Practices
- Run this instance-wide as a routine capacity check, the data/log split surfaces problems a single free-space number would hide
- Sort by Free % rather than raw free space when comparing databases of very different sizes
- Cross-reference low Free % findings with Database Growth Risk and Forecast to see whether it’s trending toward a real problem or already stable
- Re-run after any planned file grow or shrink to confirm the change had the intended effect
Related Scripts
You may also find these scripts useful:
- Storage and Capacity (hub)
- Database Files Detail
- Filegroup Space
- Database Growth Risk and Forecast
- DBA Scripts: The Complete Guide, the map across every script on this site
Frequently Asked Questions
Why does this need to switch database context with a cursor instead of one cross-database query?
FILEPROPERTY(name, 'SpaceUsed') only resolves correctly against the database it’s actually running in, there’s no single system view that reports accurate used-space-per-file across every database from one context. The cursor-and-sp_executesql pattern is the standard, reliable way to collect this cross-database.
Does a high Free % always mean a database is over-provisioned?
Not necessarily, some free space headroom is intentional, avoiding frequent autogrowth events during peak load. The concern is a database trending toward 0% free rather than one sitting comfortably at 40-60%.
Summary
Total free space alone hides more than it reveals, a database can look fine on one number while its log or its data files are individually close to full. This script splits the picture cleanly across every database in one pass, human-readable and ready for a routine capacity review.
Run it instance-wide as a standing check, and treat any database with low Free % on either data or log as worth a closer look before it becomes an outage.
Leave a Reply