How to Get Space Used by Database Objects

For one table, or for the database as a whole, run sp_spaceused in the database you care about:

USE SalesDB;
GO
EXEC sp_spaceused;                 -- whole database
EXEC sp_spaceused N'dbo.Orders';   -- one table, including its indexes

For every table at once, sorted by size, query sys.dm_db_partition_stats instead. That query is in the "All tables, largest first" section below. The numbers shown on this page are sample output; yours will differ.

Reading the sp_spaceused output

With an object name, you get one row:

name    rows     reserved    data        index_size  unused
Orders  2500000  412736 KB   301056 KB   110592 KB   1088 KB

Column	Meaning
rows	Row count from metadata (fast, approximate)
reserved	Everything allocated to the table and its indexes
data	The table itself: the heap or the leaf level of the clustered index, plus large-object (LOB) and row-overflow pages
index_size	Nonclustered indexes, plus the upper levels of the clustered index. XML, full-text and spatial index pages are counted here too
unused	Allocated to the object but not yet holding anything

reserved = data + index_size + unused. A table with a clustered index and no other indexes still shows a small index_size, because the non-leaf levels of the clustered index are counted there.

Without arguments, you get two result sets for the database:

database_name  database_size  unallocated space
SalesDB        1536.00 MB     224.00 MB

reserved    data        index_size  unused
819200 KB   614400 KB   196608 KB   8192 KB

• database_size is the size of all data files plus the log files.
• unallocated space is free space inside the data files that no object has claimed.
• The second result set is the total across all objects.

Because database_size includes the log and the other figures cover data files only, reserved + unallocated space is normally less than database_size. In this sample the difference is a 512 MB log file.

To get the database totals as a single row, which is easier to capture into a table:

EXEC sp_spaceused @oneresultset = 1;

What sp_spaceused accepts

@objname can be a table, an indexed view or a Service Broker queue in the current database. Quote the name when it includes a schema. A name that does not exist in the current database, or an object type it does not support (an ordinary view, for example), returns:

Msg 15009, Level 16, State 1, Procedure sp_spaceused
The object 'dbo.Orderz' does not exist in database 'SalesDB' or is invalid for this operation.

Its limits are practical ones: it handles one object per call, it drops the schema name from the output, and every size is a string such as 412736 KB that you cannot sort or sum without parsing. For anything beyond a quick look, query the underlying views.

All tables, largest first

SELECT s.name AS schema_name,
       t.name AS table_name,
       SUM(CASE WHEN ps.index_id IN (0, 1) THEN ps.row_count ELSE 0 END) AS row_count,
       CAST(SUM(ps.reserved_page_count) * 8 / 1024.0 AS decimal(18,2))   AS reserved_mb,
       CAST(SUM(ps.used_page_count)     * 8 / 1024.0 AS decimal(18,2))   AS used_mb,
       CAST(SUM(ps.reserved_page_count - ps.used_page_count) * 8 / 1024.0
            AS decimal(18,2))                                            AS unused_mb
FROM sys.dm_db_partition_stats AS ps
JOIN sys.tables  AS t ON t.object_id = ps.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
GROUP BY s.name, t.name
ORDER BY SUM(ps.reserved_page_count) DESC;

schema_name  table_name  row_count  reserved_mb  used_mb  unused_mb
dbo          Orders      2500000    403.06       402.00   1.06
dbo          OrderLines  9800000    388.50       387.91   0.59
dbo          Customers   120000     21.13        20.95    0.18

How it works:

• The view has one row per partition of every heap and index. Summing over all rows for an object gives the table plus all its indexes.
• A page is 8 KB, so pages × 8 is kilobytes.
• Rows are counted from index_id 0 (heap) or 1 (clustered index) only. Each nonclustered index stores the rows again, so summing every index would multiply the count.
• reserved_page_count already includes in-row, LOB and row-overflow pages.

This query does not include the internal tables behind XML, spatial and full-text indexes, which sp_spaceused adds to its figures. If a table has those, the two results differ slightly.

Space per index

To see which index is taking the space on one table:

SELECT i.index_id,
       i.name      AS index_name,       -- NULL for a heap
       i.type_desc,
       SUM(ps.row_count) AS row_count,
       CAST(SUM(ps.used_page_count) * 8 / 1024.0 AS decimal(18,2)) AS used_mb
FROM sys.dm_db_partition_stats AS ps
JOIN sys.indexes AS i
  ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.object_id = OBJECT_ID(N'dbo.Orders')
GROUP BY i.index_id, i.name, i.type_desc
ORDER BY i.index_id;

A filtered index shows fewer rows than the table. A large, rarely used nonclustered index is often the cheapest space to win back.

Space per file, and the log

Objects live inside data files, and a file can be far larger than its contents. To compare file size with space used:

SELECT name,
       type_desc,
       size * 8 / 1024                          AS size_mb,
       FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS used_mb
FROM sys.database_files;

size and FILEPROPERTY(..., 'SpaceUsed') are both counts of 8 KB pages. For the transaction log, sys.dm_db_log_space_usage reports the current database, and DBCC SQLPERF(LOGSPACE) lists the log size and percentage used for every database on the instance.

When the numbers look wrong

Stale metadata. The page and row counts are kept in metadata and are documented as approximate. They can be off, typically after dropping an index or in a database upgraded from a very old version. Correct them with:

EXEC sp_spaceused N'dbo.Orders', @updateusage = N'TRUE';
-- or for the whole database
DBCC UPDATEUSAGE (0);

Both scan the object or database, so run them outside busy hours on large tables.

Space not released right after a drop or truncate. When you drop or truncate a large table, or drop or rebuild a large index, SQL Server deallocates the pages in the background after the transaction commits. For a short time the space still shows as used.

Deleting rows did not shrink the table. DELETE empties pages, but they often stay allocated to the table, especially on a heap. That shows up as a growing gap between reserved and used, or as low page density. ALTER INDEX ... REBUILD (or ALTER TABLE ... REBUILD for a heap) compacts it.

The files did not get smaller. Freeing space inside the database moves it to unallocated space; the files keep their size. Shrinking files is a separate operation (DBCC SHRINKFILE) that fragments indexes and is rarely worth doing unless the space will not be needed again.

Compressed and columnstore tables report their on-disk, compressed size. That is the number that matters for storage, but it says little about how much data the table logically holds.

Memory-optimized tables are not covered by the per-table figures. sp_spaceused reports their checkpoint-file storage only at database level, through @include_total_xtp_storage = 1 together with @oneresultset = 1.

Permissions

• sp_spaceused can be run by any database user (it is granted to public). Only members of db_owner can use @updateusage.
• sys.dm_db_partition_stats requires VIEW DATABASE STATE on the database. On SQL Server 2022 and later the more granular VIEW DATABASE PERFORMANCE STATE is sufficient.
• DBCC UPDATEUSAGE requires sysadmin or db_owner.

Other ways to see it

In SQL Server Management Studio, right-click the database and choose Reports > Standard Reports > Disk Usage by Top Tables for the same information without writing a query.

You will still find scripts built on sp_MSforeachtable that call sp_spaceused for each table. That procedure is undocumented and unsupported; the single query against sys.dm_db_partition_stats above is faster and returns sortable numbers.

Further reading

• sp_spaceused (Transact-SQL)
• sys.dm_db_partition_stats (Transact-SQL)
• DBCC UPDATEUSAGE (Transact-SQL)
• sys.database_files (Transact-SQL)