Database Sizes
7September 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.
SQL Studies 
nice script, another way, you can also use “group by rollup” to calculate it
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.
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.
Good point! I was only using this for user files so didn’t remember to mention that. Thanks!
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.
Perfectly reasonable!
[…] Database sizes […]