SYSK 360: What’s taking up the space in my database? Also, a word about SQL reports…

  • Article

So, you’ve updated usage stats on your database by running

EXEC sp_spaceused @updateusage = N'TRUE'

 

But, you still can’t figure out why it’s reporting much more used space then the data that appears in your tables…

Try running the following query to get number of rows and detailed space usage information for each table and each index.

SELECT object_name(object_id) as ObjName,

      index_id as IndexID,

      sum(in_row_data_page_count) as InRowDataPages,

      sum(lob_used_page_count) as LargeObjectPages,

      sum(row_overflow_used_page_count) as RowOverflowPages,

      sum(row_count) as Rows

FROM sys.dm_db_partition_stats

GROUP BY object_id,index_id

GO

Also, there are a number of commonly used reports that are available in SQL Server Management Studio. Just right-mouse click on the database, and choose Reports -> Standard Reports…

While I am on the SQL reports topic, you can also get a number of useful reports for the entire server, e.g. All Blocking Transactions, Top Queries by Average CPU time, Top Queries by Average IO, Schema Changes History, Memory Consumption and many more! Simply, right-mouse click on the server name and choose Reports ->Standard Reports…

Finally, one more query that can come in handy:

SELECT * FROM fn_trace_gettable ('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default) GO

 

Special thanks to Curtis Krumel and Saleem Hakani for the queries above!