Database Sizes

7

September 14, 2021 by Kenneth Fisher

Quick query today. I needed a list of database sizes so came up with this:

SELECT db.name AS Database_Name
	,SUM(CASE WHEN type = 0 THEN size*8.0/1024 ELSE 0 END) AS DataSizeMB
	,SUM(CASE WHEN type = 1 THEN size*8.0/1024 ELSE 0 END) AS LogSizeMB
	,SUM(CASE WHEN type = 2 THEN size*8.0/1024 ELSE 0 END) AS FileStreamSizeMB
	,SUM(CASE WHEN type = 4 THEN size*8.0/1024 ELSE 0 END) AS FullTextSizeMB
	,SUM(size*8.0/1024) AS TotalSizeMB
FROM sys.master_files files
JOIN sys.databases db
	ON files.database_id = db.database_id
GROUP BY db.database_id, db.name

Nothing exciting, but I figure if I needed it someone else will too. If you’ve got a better way to handle this please feel free to share.

7 thoughts on “Database Sizes

  1. victor says:

    nice script, another way, you can also use “group by rollup” to calculate it

  2. Brian Walker says:

    You might like the Databases tool of SQLFacts, a FREE suite of tools.

    http://www.SQLFacts.com

    It’s a much longer script, but it provides a lot of information.

  3. Brian Walker says:

    Something to be aware of…
    While sys.master_files returns the current size for most databases, it does not return the current size for tempdb. Instead, it returns the size for tempdb as it’s recreated on startup.

  4. Randy says:

    Nice script. I modified it by adding “ORDER BY db.name” at the end to make it easier to find a particular database in the list.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,614 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: