How Much Space Am I Taking Up?

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 ;