Database Sizes
Leave a commentSeptember 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.
