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 ;
Comments
Looks like you have a type on line 9 “(size * / 1024 AS size_in_mb”. Something must have gotten lost in the post.
This is nice, Jeremiah. Line 09 has syntax issue (open paren without close, both a multiplication and division operator).
Another thing I changed was on line 08, I used the UPPER function so that the drive letter would not be case-sensitive… on my environment the drive letters had mixed case (both “E:” and “e:” appear). This lets the GROUP BY clauses make more sense because you don’t need E: and e: to be grouped separately.
Thanks!
@AlanBarber, @Noel – thanks for spotting the syntax issue. Turns out my syntax highlighter hates the combination of 8 and ). Thinks I’m trying to smile or something…
@Noel – I hadn’t thought of case sensitivity. Thanks for reminding me that the rest of the world doesn’t live in a caseless bubble. I’ll update the script accordingly.
One more..how much space is left on each drive
master..xp_fixeddrives
I can see you love temp tables as much as I do. I’ve really come to appreciate table variables in cases like this — not having to worry about whether you need to drop a temp table: priceless.
Funnily enough, I despise table variables and love temp tables.
Funny way of showing it.
tough love
One more: how full are the files on each drive? Or in other words, how much space is free across all the files on the drive.
Thinking about this mostly in terms of seeing when more space will soon be needed. I also could use it for a troubleshooting guide for summarizing information when responding to disk alerts (although I don’t want the default behavior to be “shrink the file with the most free space” for obvious reasons).