Finding All Database Sizes on a SQL Server

Are you curious about what databases are taking up the most space on a SQL Server? If you only have a handful of databases or if it’s a server you frequently work on then you can probably make a good guess as to which databases are largest. If you are on an unfamiliar SQL Server or if you just want to confirm sizes, there are a couple of quick ways to find out.

Keep it Simple with sp_databases

First, in SSMS it can’t get much more simple than running:

EXEC sp_databases;

This procedure will give you the basics without a bunch of extra columns or details. You’ll get DATABASE_NAME, DATABASE_SIZE (shown in KB), and REMARKS. REMARKS should return NULL as Microsoft states “For the Database Engine, this field always returns NULL.”

What About Database File Sizes?

Sometimes you may want to check the size of each database file instead of the total of the files making up the database. If you’re looking for more specific information about individual database files, you can run the following script:

SELECT name as 'DATABASE_NAME', size as 'DATABASE_SIZE', size * 8/1024 as 'DATABASE_SIZE_MB'
FROM sys.master_files
ORDER BY size DESC;
GO

The DATABASE_SIZE column above is the size in 8-KB pages but to make it a bit easier to read we can convert that to MB and include that as the DATABASE_SIZE_MB column.

Finding Size With Object Explorer Details

What happens if your keyboard suddenly breaks and all you have is a mouse to navigate SSMS? Or maybe you’re just tired of running scripts for the day? Have no fear, Object Details has you covered. In SSMS, click View, click Object Explorer Details, and double click Databases in the Object Explorer Details window. Right click on one of the columns to bring up a list of additional columns and select Size (MB) to add the size as a column.

The column will be added to the end of the columns. You may want to click and drag it closer to the database name if you already have a couple columns viewable:

From here you can click the column names to sort ascending or descending as desired.

Thanks for reading!