Part of the DBA-Tools Project.
The Filegroup That’s Actually About to Run Out
Database Free Space Summary tells you a database has plenty of room overall. That can still be wrong for a specific filegroup: a database on multiple filegroups can look comfortable in total while its PRIMARY filegroup, or a specific secondary filegroup an application actually writes to, is nearly full.
Autogrow acts at the file level, inside a filegroup, not at the database level. A database-wide free space number does not tell you which filegroup is about to hit that wall.
This script queries every online database and returns allocated, used, and free space per filegroup, ordered so the tightest filegroup across the whole instance is at the top.
Why Filegroup Space Matters
- Autogrow, and any failure to grow, happens per file within a filegroup, a database-level free space total can hide a filegroup that’s genuinely out of room
- Multi-filegroup databases (common for partitioned or archived data) can have wildly different free space per filegroup, they don’t fill evenly
- Read-only filegroups (
is_read_only) are worth knowing about separately, a “full” read-only filegroup is expected and not an incident - This is a cross-database, cross-filegroup view in one pass, ordered by lowest free % first, so the tightest spot on the whole instance surfaces immediately
When to Run This Script
- Routine SQL Server health checks, especially on instances using multiple filegroups
- Investigating an autogrow failure or insert error that Database Free Space Summary alone didn’t explain
- Before a bulk load or large index rebuild targeted at a specific filegroup
- Capacity planning for databases using filegroup-based partitioning or archiving
The Script
/*
Script Name : Get-FilegroupSpace
Category : monitoring
Purpose : Allocated, used, and free space per filegroup across all online databases — ordered by lowest free percentage first.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-filegroup-space/)
Requires : VIEW ANY DATABASE, VIEW DATABASE STATE
*/
-- Blog: https://sqldba.blog/dba-scripts-get-filegroup-space/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#FgSpace') IS NOT NULL DROP TABLE #FgSpace;
CREATE TABLE #FgSpace (
database_name NVARCHAR(128) NOT NULL,
filegroup_name NVARCHAR(128) NOT NULL,
filegroup_type VARCHAR(20) NOT NULL,
is_default BIT NOT NULL,
is_read_only BIT NOT NULL,
file_count INT NOT NULL,
size_mb DECIMAL(20,2) NOT NULL,
used_mb DECIMAL(20,2) NOT NULL
);
DECLARE @db NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);
DECLARE fg_cur CURSOR FAST_FORWARD FOR
SELECT name FROM sys.databases
WHERE state_desc = 'ONLINE'
AND database_id <> 2 /* exclude tempdb — no meaningful filegroup data */
AND HAS_DBACCESS(name) = 1
ORDER BY name;
OPEN fg_cur; FETCH NEXT FROM fg_cur INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'USE ' + QUOTENAME(@db) + N';
INSERT INTO #FgSpace (database_name, filegroup_name, filegroup_type,
is_default, is_read_only, file_count, size_mb, used_mb)
SELECT
DB_NAME(),
fg.name,
CASE fg.type
WHEN ''FG'' THEN ''ROWS''
WHEN ''FD'' THEN ''FILESTREAM''
WHEN ''FX'' THEN ''MEMORY''
ELSE fg.type
END,
fg.is_default,
fg.is_read_only,
COUNT(f.file_id),
CAST(SUM(CAST(f.size AS BIGINT) * 8.0 / 1024) AS DECIMAL(20,2)),
CAST(SUM(CAST(COALESCE(FILEPROPERTY(f.name, ''SpaceUsed''), 0) AS BIGINT) * 8.0 / 1024) AS DECIMAL(20,2))
FROM sys.filegroups fg
JOIN sys.database_files f ON f.data_space_id = fg.data_space_id
GROUP BY fg.name, fg.type, fg.is_default, fg.is_read_only;';
BEGIN TRY EXEC sp_executesql @sql; END TRY BEGIN CATCH END CATCH;
FETCH NEXT FROM fg_cur INTO @db;
END;
CLOSE fg_cur; DEALLOCATE fg_cur;
SELECT
database_name,
filegroup_name,
filegroup_type,
is_default,
is_read_only,
file_count,
size_mb,
used_mb,
CAST(size_mb - used_mb AS DECIMAL(20,2)) AS free_mb,
CAST(CASE WHEN size_mb > 0 THEN (size_mb - used_mb) * 100.0 / size_mb ELSE 0 END
AS DECIMAL(5,1)) AS free_pct
FROM #FgSpace
ORDER BY free_pct ASC, database_name, filegroup_name;
DROP TABLE #FgSpace;
The database context switch happens per-database via a cursor because filegroup and file metadata only resolve correctly against the database they belong to. Results are ordered by free % ascending, so the single tightest filegroup on the instance is always the first row.
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
# Free space per filegroup, across every online database:
.\run.ps1 Get-FilegroupSpace
# To run against a remote sql server:
.\run.ps1 Get-FilegroupSpace -ServerInstance SQLSERVER01
This script lives in the repo at:
Example Output
Real output from this lab instance, not staged (23 filegroups, condensed to the tightest and a couple of contrasts):
| database_name | filegroup_name | size_mb | free_mb | free_pct |
|---|---|---|---|---|
| migdb_751FB44B | PRIMARY | 790.00 | 4.19 | 0.50 |
| migdb_A9250FB6 | PRIMARY | 790.00 | 4.19 | 0.50 |
| migdb_B8A15B78 | PRIMARY | 790.00 | 4.19 | 0.50 |
| WatchtowerMetrics | PRIMARY | 2440.00 | 51.31 | 2.10 |
| GrowthLab | PRIMARY | 1736.00 | 843.19 | 48.60 |
The three migdb_* databases at 0.50% free are the genuine finding here, every one of them a single-file PRIMARY filegroup with under 5 MB of headroom, the kind of result that would be completely invisible from a database-level total if these databases also had roomy secondary filegroups (they don’t, in this case, which makes it worse, not better).
Understanding the Results
- free_pct near 0 on PRIMARY — this is usually the most urgent finding a filegroup-level view can produce, because
PRIMARYtypically holds system objects and often the default filegroup for new objects too - is_default = 1 — this filegroup receives new objects created without an explicit
ONclause; low free space here affects anything created without specifying a target filegroup - is_read_only = 1 — expect these to sit at whatever free % they were left at; a “full” read-only filegroup (common for archive data) is not an incident on its own
- Multiple rows per database — only appears for databases actually using more than one filegroup; a database with everything on PRIMARY shows exactly one row
Best Practices
- Run this alongside Database Free Space Summary, not instead of it, database-level and filegroup-level free space answer different questions
- Pay closest attention to
is_default = 1filegroups with low free %, that’s where untargeted growth lands - On databases using filegroup-based partitioning or archiving, check this regularly, since fill rate can differ enormously between an active filegroup and an archive one
- Treat a near-zero free % on a single-file, single-filegroup database as equivalent in severity to a near-full disk volume, since there is no relief valve until the file grows
Related Scripts
You may also find these scripts useful:
- Storage and Capacity (hub)
- Database Free Space Summary
- Database Files Detail
- Database Sizes and Free Space
- Disk Space
- Database Growth Risk and Forecast
Frequently Asked Questions
Why check filegroup space separately from database-level free space?
Autogrow and out-of-space errors happen at the file level, inside a specific filegroup. A database can show comfortable free space overall while one filegroup, often the default one receiving new objects, is nearly full. This script isolates that.
What does a read-only filegroup at low free % mean?
Usually nothing urgent. Read-only filegroups (common for archived partitions) are typically left at whatever fill level they were set read-only at, check is_read_only before treating a low free % as a live capacity risk.
Summary
A healthy-looking database can still have one filegroup quietly running out of room, and that’s exactly the filegroup autogrow will fail against first. This script surfaces every filegroup across every online database in one pass, ordered so the tightest spot on the instance is always the first row.
Run it alongside a database-level free space check, not as a replacement for one, and treat any near-zero free % on a default, writable filegroup as worth acting on before autogrow does it the hard way.
Leave a Reply