Database Sizes

Leave a comment

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.

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,569 other followers

Follow me on Twitter

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