Summary of disk usage for all databases using SSMS

Question

How can I see all disk usage of all my databases on a given SQL Server in one single query. I have around 15 different databases on my server and I want to see which one is using the maximum disk space.

I know I can see reports of Disk Usage per database in SSMS or logon to the server and see the size of MDF/LDF files but this seems like a pretty obvious feature that should come with SSMS and I cant seem to find it.

Answer 1

This stored procedure will help.

exec sp_helpdb;

You'll get something like this:

name      db_size       owner         dbid created     status                                                                                                                                                                                                                                                                          compatibility_level
--------- ------------- ------------- ---- ----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------- 
Database1    7262.81 MB DOMAIN\Admin  5    Aug 25 2010 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=661, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics                                                                             100
Project27   22781.81 MB DOMAIN\User42 13   Oct 13 2011 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=661, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAnsiNullsEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsQuotedIdentifiersEnabled 100
MyDBName       84.69 MB DOMAIN\Me     14   Oct 14 2011 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=661, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled                                                          100

To learn more about a particular database, do:

exec sp_helpdb DatabaseName;