How to Check Table Size in SQL Server

Why use sp_spaceused to check table size?

sp_spaceused gives an instant view of how much space a table or index consumes. It helps capacity planning, performance tuning, and identifying bloated tables.

What is the basic syntax?

EXEC sp_spaceused 'TableName'; returns rows, reserved, data, index_size, and unused in KB. Use @updateusage = 'N' | 'Y' to refresh internal metadata first.

How do I check a single ecommerce table?

Run EXEC sp_spaceused 'Orders'; to see the current size of the Orders table, including all its indexes.

How can I check all tables at once?

Run sp_MSforeachtable 'EXEC sp_spaceused ''?'''; This undocumented helper loops through every table and prints its size. Store the output in a temp table for sorting.

How do I sort tables by largest size?

INSERT the results into #sizes, then SELECT * FROM #sizes ORDER BY data_MB DESC; This quickly highlights space hogs.

When should I refresh @updateusage?

Use @updateusage = 'TRUE' after massive INSERT or DELETE operations. It walks system catalogs to correct row and page counts for accurate sizing.

What are alternative DMV methods?

sys.dm_db_partition_stats joined to sys.tables yields precise page counts. Multiply page_count * 8 / 1024 for MB. DMV queries are scriptable and avoid the PRINT output of sp_spaceused.

Example DMV Query

SELECT t.name, SUM(ps.used_page_count)*8/1024 AS data_MB FROM sys.dm_db_partition_stats ps JOIN sys.tables t ON t.object_id = ps.object_id GROUP BY t.name ORDER BY data_MB DESC;

Best practices for table-size checks

Automate weekly reports, focus on rapidly growing tables, and archive or partition data before storage limits affect performance.

Common mistakes to avoid

Skipping @updateusage causes stale size numbers. Ignoring index_size hides growth from non-clustered indexes that can outpace data.

Key takeaways

Use sp_spaceused for quick checks, sys.dm_db_partition_stats for detailed scripts, and always refresh metadata after bulk operations.

FAQ

Does sp_spaceused lock the table?

No. It reads DMVs and catalog views; locking impact is negligible.

Can I check size without sysadmin rights?

Yes, grant VIEW DATABASE STATE to allow DMV access or EXECUTE on sp_spaceused for the specific database.

How often should I audit table sizes?

Weekly for OLTP systems and daily for rapidly growing logging tables.