A database file can be large while only a few tables account for its used pages. Measuring tables per filegroup shows where allocated table and index space sits right now.
Count Tables per Filegroup by Allocation Unit
A physical file belongs to a filegroup. A table's indexes and partitions allocate pages in those filegroups. Reporting only file size cannot tell you which table or index is responsible for the allocation.
Start with sys.tables and sys.indexes, then identify their partitions in sys.partitions. sys.allocation_units describes allocated page containers. sys.filegroups gives the filegroup name attached to each allocation unit's data_space_id.
I check the allocation type before joining these views. The container identifier does not always represent the same kind of object. Treating every container_id as a partition_id produces an impressively incomplete report.
For rowstore in-row and row-overflow allocations, container_id matches the partition's hobt_id. LOB allocations use partition_id. Columnstore delta stores have their own HoBT identifiers, so include their mapping when reporting columnstore indexes.
Used pages and reserved pages also differ. total_pages represents allocated or reserved pages. used_pages reports pages currently used within those allocations. Neither value is a forecast of future growth.
Build a Small Two-Filegroup Test
Use a disposable database for this setup. The script discovers its default data path and generates the ADD FILE command for review. Confirm that the new physical filename is unused and that the SQL Server service can write there.
ALTER DATABASE [FilegroupSpaceDemo] ADD FILEGROUP [ArchiveFG];
DECLARE @Folder nvarchar(260) =
CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultDataPath'));
SELECT N'ALTER DATABASE [FilegroupSpaceDemo] ADD FILE '
+ N'(NAME = N''FilegroupSpaceDemo_Archive'', FILENAME = N'''
+ REPLACE(@Folder + N'FilegroupSpaceDemo_Archive.ndf', N'''', N'''''')
+ N''', SIZE = 16MB, FILEGROWTH = 16MB) TO FILEGROUP [ArchiveFG];'
AS ReviewedCommand;Create FilegroupSpaceDemo first through a reviewed database setup. Then inspect and execute the generated command separately. The named sizes are requested setup values, not measured storage use from an existing server.
Once the filegroup has a file, create objects with explicit placement. The LOB column ensures the test includes storage beyond ordinary in-row pages. A clustered index's placement determines the main table's in-row allocation.
USE [FilegroupSpaceDemo];
CREATE TABLE dbo.RecentDocuments
(
DocumentID int NOT NULL PRIMARY KEY,
BodyText nvarchar(max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
CREATE TABLE dbo.ArchiveDocuments
(
DocumentID int NOT NULL PRIMARY KEY,
BodyText nvarchar(max) NULL
) ON [ArchiveFG] TEXTIMAGE_ON [ArchiveFG];
INSERT dbo.RecentDocuments VALUES
(1, REPLICATE(CAST(N'r' AS nvarchar(max)), 6000));
INSERT dbo.ArchiveDocuments VALUES
(1, REPLICATE(CAST(N'a' AS nvarchar(max)), 6000));The large strings encourage LOB storage in this simple layout. Verify the actual allocations instead of claiming a specific number of pages. Empty objects and newly created indexes can have little allocated space until rows are written.
Map Each Allocation to Its Owning Partition
The following query includes heaps, indexes, LOB units, row-overflow units, and columnstore delta stores. The EXISTS branch maps a delta-store HoBT without multiplying rows when rowgroup metadata contains several entries.
SELECT s.name AS SchemaName, t.name AS TableName,
fg.name AS FilegroupName,
SUM(a.used_pages) * 8.0 / 1024 AS UsedMB,
SUM(a.total_pages) * 8.0 / 1024 AS ReservedMB
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.indexes AS i ON i.object_id = t.object_id
JOIN sys.partitions AS p
ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON
(a.type IN (1,3) AND a.container_id = p.hobt_id)
OR (a.type = 2 AND a.container_id = p.partition_id)
OR (a.type IN (1,3) AND EXISTS
(
SELECT 1 FROM sys.column_store_row_groups AS rg
WHERE rg.object_id = p.object_id
AND rg.index_id = p.index_id
AND rg.partition_number = p.partition_number
AND rg.delta_store_hobt_id = a.container_id
))
JOIN sys.filegroups AS fg ON fg.data_space_id = a.data_space_id
WHERE t.is_ms_shipped = 0 AND a.type <> 0
GROUP BY s.name, t.name, fg.name
ORDER BY ReservedMB DESC, SchemaName, TableName;Pages are eight kilobytes, so multiplying by eight and dividing by 1024 expresses these counts in megabytes. The result includes index storage associated with each table. It is not only the payload of base rows.
Do not sum p.rows across allocation units. A partition can own several units, so that join would repeat its row count. Calculate row totals independently from the heap or clustered index when the report also needs them.
The same table can appear under several filegroups. That is expected when indexes, LOB storage, or partitions use different destinations. A table name is not a promise that all its pages live together.
Inspect Partition Scheme Destinations
For an unpartitioned index, sys.indexes.data_space_id identifies its filegroup. For a partitioned index, that identifier names a partition scheme. Join through sys.destination_data_spaces to see the filegroup assigned to each partition number.
SELECT OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName, p.partition_number,
ps.name AS PartitionSchemeName,
fg.name AS PlannedFilegroup
FROM sys.indexes AS i
JOIN sys.partitions AS p
ON p.object_id = i.object_id AND p.index_id = i.index_id
LEFT JOIN sys.partition_schemes AS ps
ON ps.data_space_id = i.data_space_id
LEFT JOIN sys.destination_data_spaces AS dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
JOIN sys.filegroups AS fg
ON fg.data_space_id = COALESCE(dds.data_space_id, i.data_space_id)
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY SchemaName, TableName, i.index_id, p.partition_number;This query describes index placement, including empty partitions. The allocation query describes space already allocated. Use both when the difference between planned placement and current allocation matters.
Explain What the Numbers Leave Out
The table report does not include every internal object in the database. Version storage and other internal allocations need separate investigation. Compare your scope with the question before expecting table totals to equal every physical file size.
Deferred deallocation can also delay the release of pages after large object changes. Concurrent inserts and rebuilds alter metadata while you collect it. Record the capture time rather than treating the output as a permanent ledger.
I separate reserved space from file free space in the report. An allocated page count does not establish how much Windows storage remains. Review database-file allocation and volume capacity separately before approving growth.
Snapshot Tables per Filegroup to Track Growth
Which table has grown since last month? One current result cannot answer that question. Save comparable snapshots with database identity and capture date, then compare table and filegroup totals across those captures.
Keep the same inclusion rules for indexes and allocation types. An altered report definition can manufacture a growth trend. Record partition movement too, because space shifting between filegroups is different from more data arriving.
Use the largest allocations to guide investigation, then inspect their business retention and access patterns. Moving a table or shrinking a file requires separate planning. The first useful result is knowing where the current pages belong.
For a filegroup-only summary, group the same allocation mapping by fg.name instead of table and schema. That preserves the inclusion rules while removing the table detail. Do not add the resulting filegroup summary back to the table totals, because both describe the same allocations at different levels.
An empty table can disappear from the allocation report because the inner join requires an allocation unit. Keep a separate table inventory when every object must appear, including objects with no pages yet. A left join with careful zero handling serves that purpose, but do not let its null rows become a fictitious filegroup.
Check available metadata permissions when a result looks unexpectedly small. Catalog visibility depends on the objects the collecting account can see. Capture using the same approved account across snapshots, or record changes in visibility alongside the apparent storage change.
If a filegroup contains several files, the allocation report still groups their space together. Finding the exact distribution across files requires another level of inspection. A filegroup can have free space even while one physical file has a different growth history from its neighbors.
Keep reserved and used columns side by side when discussing capacity. Their difference is space inside allocations, not necessarily space Windows can reclaim. A file shrink needs its own assessment of free extents, future growth, and operational cost before you turn a table report into a maintenance action. A report of tables per filegroup should retain its allocation and partition context. Recheck tables per filegroup after storage changes rather than treating an old inventory as permanent placement evidence.
Related reading on this blog: Script to Get Partition Info Using DMV and Making Table Read Only via FileGroup.
A filegroup total is not a growth history, it is a map of the allocations visible at one moment.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
