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?
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
This post might give you an alternative approach.
I have found sys.database_files is pretty much reliable.
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;