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?

  • Try EXEC sys.sp_spaceused @objname = N'dbo.SomeTable', @updateusage = 'true'; – Aaron Bertrand Apr 10 '15 at 13:35
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
  • 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.) – Stackoverflowuser Apr 13 '15 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;
  • 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. – LowlyDBA Apr 10 '15 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 '15 at 14:15
  • You can edit your post to make that a bit easier on the eyes – LowlyDBA Apr 10 '15 at 14:16
  • 1
    Note that this doesn't report anything about space used by individual tables. – Aaron Bertrand Apr 10 '15 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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