Different table size on sp_spaceused and standard report "Disk Usage by Top Tables"

Question

I ran sp_spaceused and Disk Usage by Top Tables standard report for a table.

The results for sp_spaceused are:

name rows reserved data index_size unused
SomeTable <1301755> <7691344 KB> <3931672 KB> <3673840 KB> <85832 KB>

However Disk Usage by Top Tables report shows:

Table Name  # Records   Reserved (KB)       Data (KB)   Indexes (KB)    Unused (KB)     
SomeTable   1.301.755   4.340.216       3.931.672   324.776 83.768      

Record count is the same but there is a big gap between the two in terms of space used. sp_spaceused shows 7.691.344 KB as reserved while the report shows 4.340.216 KB. Which one is correct?

Answer 1

Frankly, I wouldn't use either. You can find your biggest tables immediately - with more flexibility - and not worry about where @updateusage has been set.

CREATE PROCEDURE dbo.TopTables
  @NumberOfObjects INT = 100,
  @MinimumSizeInMB INT = 10
AS
BEGIN
  SET NOCOUNT ON;

  SELECT TOP (@NumberOfObjects)
    [object] = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name),
    index_count = COUNT(i.index_id),
    size_in_MB = SUM(p.reserved_page_count)*8/1024.0
  FROM sys.schemas AS s
  INNER JOIN sys.objects AS t
  ON s.[schema_id] = t.[schema_id]
  INNER JOIN sys.indexes AS i
  ON t.[object_id] = i.[object_id]
  INNER JOIN sys.dm_db_partition_stats AS p
  ON t.[object_id] = p.[object_id]
  AND i.index_id = p.index_id
  WHERE t.is_ms_shipped = 0
  GROUP BY s.name, t.name
  HAVING SUM(p.reserved_page_count)*8/1024.0 >= @MinimumSizeInMB
  ORDER BY size_in_MB DESC;
END
GO

Answer 2

This post might give you an alternative approach. I have found sys.database_files is pretty much reliable.

https://stackoverflow.com/questions/9630279/listing-information-about-all-database-files-in-sql-server

Select 
    DB_NAME() AS [DatabaseName], 
    Name, 
    physical_name, 
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) 
        as decimal(18,2)) as nvarchar) Size, 
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2))
       - Cast(FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024.0 
       as decimal(18,2)) as nvarchar) As FreeSpace 
From sys.database_files;