How Large are my Databases

Simple script I put together to determine how large my databases are, looking at all of the databases in my instance.

SELECT  D.name ,
        ( 8 * SUM(MF.size) / ( 1024 ) ) AS Size_in_MB ,
        CASE WHEN D.recovery_model = 1 THEN 'FULL'
             ELSE CASE WHEN D.recovery_model = 3 THEN 'SIMPLE'
                  END
        END AS RecoveryModel
FROM    sys.master_files AS MF
        JOIN sys.databases AS D ON MF.database_id = D.database_id
GROUP BY D.recovery_model ,
        D.name
ORDER BY SUM(MF.size) DESC

Some notes. The sys.master_files will return one row for each file, hence the need to aggregate the size. That way, you end up with the sum of the sizes for all data files plus the log file for each database. Also, the size column is in 8K pages, so I multiply by 8 to get the total kilobytes then divide by 1,024 to get the total MB. For my purposes, I also needed the recovery model.
Share this:
Click to share on Twitter (Opens in new window)
Click to share on LinkedIn (Opens in new window)
Click to email a link to a friend (Opens in new window)