1

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?

1
  • Try EXEC sys.sp_spaceused @objname = N'dbo.SomeTable', @updateusage = 'true'; Apr 10, 2015 at 13:35

2 Answers 2

2

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
1
  • Thanks @Aaron. I observed that reserved space reported by sp_spaceused is erratic, It is consistent with your query with some of the tables while totally different with some. It might be something to do with the table definitions or data in it (full text indexes, row overflow data, varchar(max) etc.. not sure but sure enough to use your version.) Apr 13, 2015 at 7:49
0

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;
4
  • Peter, try to avoid posting link-only answers. If the link dies then the post becomes useless. Check out how to write a good answer in the help center. Apr 10, 2015 at 14:12
  • True, sorry. Try this ;) 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
    – Peter
    Apr 10, 2015 at 14:15
  • You can edit your post to make that a bit easier on the eyes Apr 10, 2015 at 14:16
  • 1
    Note that this doesn't report anything about space used by individual tables. Apr 10, 2015 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.