snippetsqlMinor

How to identify the top disk space consuming tables by file?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

tablesthetopspacediskconsumingfileidentifyhow

Problem

Is there a query I can run to see the top disk space consuming tables for a given file in a file group?

Need to free up some disk space for a given SAN drive and need to identify which tables are using the most space for a couple of data files on that drive.

Solution

You may go to the report --> standard report and select Disk Usage by Top Table. This will be give you the table name in descending order which is used from maximum on disk to minimum on disk.

Alternatively, you may use the SQL below to get details:

SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as Size
FROM sys.dm_db_partition_stats, sys.objects 
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id 
GROUP BY sys.objects.name
ORDER BY Size DESC;
GO

Or this query:

SELECT TOP 1000
        a3.name AS SchemaName,
        a2.name AS TableName,
        a1.rows as Row_Count,
        (a1.reserved )* 8.0 / 1024 AS reserved_mb,
        a1.data * 8.0 / 1024 AS data_mb,
        (CASE WHEN (a1.used ) > a1.data THEN (a1.used ) - a1.data ELSE 0 END) * 8.0 / 1024 AS index_size_mb,
        (CASE WHEN (a1.reserved ) > a1.used THEN (a1.reserved ) - a1.used ELSE 0 END) * 8.0 / 1024 AS unused_mb

    FROM    (   SELECT
                ps.object_id,
                SUM ( CASE WHEN (ps.index_id  N'S' and a2.type <> N'IT'   
    order by a1.data desc

Note: I have not tested these queries myself, confirming back whether it works or not would be appreciated.

This has been asked on multiple forums and I have used answers from here.

As far as files are concerned, you may need to list filegroups, if none are defined, all the allocations will take place in primary filegroup.

Below query could help you finding physical location:

SELECT  OBJECT_NAME(i.id)   AS [Table_Name]
       , i.indid
       , i.[name]           AS [Index_Name]
       , i.groupid
       , f.name             AS [File_Group]
       , d.physical_name    AS [File_Name]
       , s.name             AS [Data_Space]
FROM        sys.sysindexes i
INNER JOIN  sys.filegroups f        ON  f.data_space_id = i.groupid 
                                    AND f.data_space_id = i.groupid
INNER JOIN  sys.database_files d    ON  f.data_space_id = d.data_space_id
INNER JOIN  sys.data_spaces s       ON  f.data_space_id = s.data_space_id
WHERE       OBJECTPROPERTY(i.id, 'IsUserTable') = 1
ORDER BY    f.name, OBJECT_NAME(i.id), groupid

Hope this helps.

Code Snippets

SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as Size
FROM sys.dm_db_partition_stats, sys.objects 
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id 
GROUP BY sys.objects.name
ORDER BY Size DESC;
GO

SELECT TOP 1000
        a3.name AS SchemaName,
        a2.name AS TableName,
        a1.rows as Row_Count,
        (a1.reserved )* 8.0 / 1024 AS reserved_mb,
        a1.data * 8.0 / 1024 AS data_mb,
        (CASE WHEN (a1.used ) > a1.data THEN (a1.used ) - a1.data ELSE 0 END) * 8.0 / 1024 AS index_size_mb,
        (CASE WHEN (a1.reserved ) > a1.used THEN (a1.reserved ) - a1.used ELSE 0 END) * 8.0 / 1024 AS unused_mb

    FROM    (   SELECT
                ps.object_id,
                SUM ( CASE WHEN (ps.index_id < 2) THEN row_count    ELSE 0 END ) AS [rows],
                SUM (ps.reserved_page_count) AS reserved,
                SUM (CASE   WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                            ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END
                    ) AS data,
                SUM (ps.used_page_count) AS used
                FROM sys.dm_db_partition_stats ps
                GROUP BY ps.object_id
            ) AS a1

    INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )

    INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)

    WHERE a2.type <> N'S' and a2.type <> N'IT'   
    order by a1.data desc

SELECT  OBJECT_NAME(i.id)   AS [Table_Name]
       , i.indid
       , i.[name]           AS [Index_Name]
       , i.groupid
       , f.name             AS [File_Group]
       , d.physical_name    AS [File_Name]
       , s.name             AS [Data_Space]
FROM        sys.sysindexes i
INNER JOIN  sys.filegroups f        ON  f.data_space_id = i.groupid 
                                    AND f.data_space_id = i.groupid
INNER JOIN  sys.database_files d    ON  f.data_space_id = d.data_space_id
INNER JOIN  sys.data_spaces s       ON  f.data_space_id = s.data_space_id
WHERE       OBJECTPROPERTY(i.id, 'IsUserTable') = 1
ORDER BY    f.name, OBJECT_NAME(i.id), groupid

Context

StackExchange Database Administrators Q#282433, answer score: 4

Revisions (0)

No revisions yet.