Facility9

Are you running out of space? Filling up drives? Don’t know where to go? Is your SAN administrator complaining about your continued demands for more spindles? Do you need to juggle databases around on different servers but don’t know how you’re going to figure out the size of your index, table, and materialized view filegroups as well as the filegroups you’ve created for every schema just to piss off the DBA?

Take a step back and relax. I’ve got it covered.

-- i hate you too, temp table
IF OBJECT_ID(N'tempdb..#db_files') IS NOT NULL
  DROP TABLE #db_files ;

-- load up the temp table
SELECT  DB_NAME([dbid]) AS database_name ,
        [filename] ,
        UPPER(LEFT([filename], 1)) AS drive_letter ,
        ( size * 8 ) / 1024 AS size_in_mb
INTO    #db_files
FROM    sys.sysaltfiles AS saf
ORDER BY saf.[dbid]

-- how full is each drive?
SELECT  drive_letter ,
        SUM(size_in_mb) AS size_in_mb
FROM    #db_files AS df
GROUP BY drive_letter
ORDER BY drive_letter ;

-- how big is each database?
SELECT  COALESCE(database_name, 'RESOURCE DB') AS database_name ,
        SUM(size_in_mb) AS size_in_mb
FROM    #db_files AS df
GROUP BY database_name
ORDER BY database_name ;

-- what do the details look like?
SELECT  database_name ,
        [filename] ,
        size_in_mb
FROM    #db_files ;