Part of the DBA-Tools Project.
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
/*
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
*/
-- Blog: https://sqldba.blog/dba-scripts-get-database-free-space-summary/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
/* Set to 1 to hide master/model/msdb/tempdb */
DECLARE @ExcludeSystemDBs BIT = 0;
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
);
INSERT INTO #LogSpace
EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS');
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;
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;
;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,
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 %],
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 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],
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 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],
TotalAllocMB,
TotalUsedMB,
TotalFreeMB
FROM calc
ORDER BY TotalFreeMB DESC;
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
Real output from this lab instance, not staged (24 databases, condensed here):
| DatabaseName | Total Allocated | Total Free | Free % | Data Free | Log Free |
|---|---|---|---|---|---|
| DemoDatabase | 2.50 GB | 2.48 GB | 99.06 | 2.00 GB | 493.05 MB |
| WatchtowerMetrics | 3.67 GB | 1.34 GB | 36.36 | 51.31 MB | 1.29 GB |
| GrowthLab | 3.08 GB | 906.35 MB | 28.75 | 843.19 MB | 63.16 MB |
| migdb_A9250FB6 | 950.00 MB | 159.30 MB | 16.77 | 4.19 MB | 155.11 MB |
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:
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