A table size report says 80 GB, sp_spaceused says 73 GB, and a quick DMV query says 40 GB. The numbers can all describe different parts of the same object. Separate reserved, used, data, index, and unused space before changing a capacity plan.
Start With the Scope of Each Table Size Number
A database file includes allocated space that no table currently owns, and the database also has log files. A table's reserved size counts pages assigned to that object. Used counts pages containing data or index structures. Unused is reserved minus used. Disk Usage reports can summarize different scopes and refresh at different times. Compare like with like and record the collection time.
I have seen a team plan a disk expansion based on a sum that mixed object reserved pages with the log file total. That double-counted a story that never existed. Is the question about a table's footprint, free space inside the data files, or bytes consumed on the drive? Choose the unit first.
Ask sp_spaceused for the Object
Run sp_spaceused with a schema-qualified object name. It reports rows, reserved, data, index_size, and unused. The values are formatted in KB and based on metadata that can lag after some operations. Run it in the correct database. Do not turn on @updateusage casually for a large production database; it can scan pages and take time.
EXEC sys.sp_spaceused @objname = N'dbo.SalesHistory';Save the result with a timestamp. If a report uses a different database or a synonym, the names can look identical while the objects differ. Check OBJECT_ID and schema before comparing. The row count is approximate metadata in many catalog-based views, so do not use it as a financial count of transactions.
Rebuild Table Size Figures From Partition Stats
sys.dm_db_partition_stats supplies page counts per partition and index. Include every index for reserved and used, but use index_id 0 or 1 for the base row count. Data pages include in-row leaf data, LOB, and row-overflow pages for the heap or clustered index. Index size is used pages minus that data component. Multiply pages by 8 KB for a sp_spaceused-like layout.
DECLARE @object_id int = OBJECT_ID(N'dbo.SalesHistory', N'U');
SELECT SUM(CASE WHEN index_id IN (0,1) THEN row_count ELSE 0 END)
AS row_count_approx,
SUM(reserved_page_count) * 8 AS reserved_kb,
SUM(CASE WHEN index_id IN (0,1)
THEN in_row_data_page_count + lob_used_page_count
+ row_overflow_used_page_count
ELSE 0 END) * 8 AS data_kb,
(SUM(used_page_count) -
SUM(CASE WHEN index_id IN (0,1)
THEN in_row_data_page_count + lob_used_page_count
+ row_overflow_used_page_count
ELSE 0 END)) * 8 AS index_kb,
(SUM(reserved_page_count) - SUM(used_page_count)) * 8
AS unused_kb
FROM sys.dm_db_partition_stats
WHERE object_id = @object_id;For ordinary rowstore tables, this aligns the same categories that sp_spaceused reports. On a test table with a varchar(max) column and one nonclustered index, both returned identical reserved, data, index, and unused figures. Specialized objects such as XML, spatial, columnstore, and memory-optimized structures can need additional interpretation. If the figures differ, check object type, recent DDL, concurrent growth, and whether usage metadata needs correction.
Understand Where LOB Pages Went
A quick query that sums only in_row_data_page_count misses out-of-row varchar(max), nvarchar(max), varbinary(max), XML, and other LOB allocations. A table with long documents can therefore appear much smaller than it is. lob_used_page_count and row_overflow_used_page_count are separate columns. Include them when explaining a table's data footprint.
SELECT index_id,
SUM(in_row_data_page_count) * 8 AS in_row_kb,
SUM(lob_used_page_count) * 8 AS lob_kb,
SUM(row_overflow_used_page_count) * 8 AS overflow_kb
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(N'dbo.SalesHistory')
GROUP BY index_id ORDER BY index_id;LOB pages can belong to nonclustered or specialized indexes too, depending on design. The category formula above separates base data from other used pages; label the result honestly. An index that includes a large variable-length column can use substantial space even when its key is narrow.
Explain Space After Deletes
Deleting rows does not immediately shrink database files. Pages can remain reserved to a table, and free space inside a data file is available for reuse by the database. Ghost cleanup, page density, and index fragmentation affect when pages are freed or reused. A high unused figure after a purge is not automatically wasted disk that should be shrunk away.
I compare reserved and used before and after the purge, then file free space and future growth. If a table will refill next month, keeping reusable space can be sensible. Shrinking a data file to make a chart look smaller can cause expensive regrowth and fragmentation. Capacity planning should include expected workload, not only today's page count.
Match Table Size Reports Before Acting
Run sp_spaceused and the DMV query close together in a quiet window. Convert all figures to the same unit and label data, index, reserved, and unused precisely. If a Disk Usage report still differs, inspect its scope, refresh time, and whether it includes internal objects or whole database files. Capture the underlying query when possible.
A valid capacity recommendation separates file size, allocated object pages, and available space. I put those three numbers on one page instead of arguing over which interface is right. Once the scopes match, the apparent conflict usually turns into a simple accounting identity: reserved equals used plus unused, and used includes both data and index structures.
Read the Accounting Identity
For the same object and moment, reserved pages equal used pages plus unused pages. Used pages include data leaf pages and index or management pages. The formula is simple, but the labels in a report can hide which page families were counted. Run the DMV query by index_id before summing if one secondary index is unexpectedly large. A high index component can come from several wide covering indexes, not from the table's base rows.
Check page density as well as page count. Two tables with equal row counts can have very different sizes because of variable-length values, fill factor, fragmentation, compression, or LOB storage. A size change after an index rebuild can reflect page packing rather than a change in business rows. Pair storage numbers with schema and maintenance history.
Separate File Free Space
Unused reserved pages are still assigned to the object. Free space in a data file is not assigned to that table and can serve other objects. Free space on the Windows volume is one level farther out. Those three numbers answer different planning questions. To forecast drive growth, combine data-file free space, expected object growth, autogrowth increments, and log demand. Avoid adding table reserved totals and calling the sum a drive-space forecast.
Related reading on this blog: Correcting Space Allocation with DBCC UPDATEUSAGE and Available Free Space in Data and Log File.
A table-size number is not universal, it is a measurement of selected pages at one time.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
