Ok, so just as an aside, I didn’t find out until later that night that Jen and I posted on the exact same topic as a mere coincidence. She thought I had taken her topic and exploded it into PS, but I didn’t even know she wrote about it until she said something. So here’s hoping we’re on separate topics today…
Oh y, and you can see both of the other posts here: Mine and Hers.

So on to today’s topic. Today I wanna talk about getting the size of offline DBs. I’ve got a use case right now where we’ve got dozens of DBs on a server that are offline and we need to know how much space we’ll gain if we delete them. You can’t get space through normal means because they’re offline. So the next best thing you can do is to get the size from the files themselves. Here you’ve got 2 choices, but only one is really viable.
First, you can go look at the OS and physically see what the file sizes are. If you’ve got a lot this isn’t workable in the least.

However, if you query sys.master_files you can get the size in 8K pages and then do some math to get a good idea of how much space you’ll get back if you were to delete them. Here I’ve broken it down for you by drive but you can just take out the drive col and get the total. Now, this has one big limitation that it doesn’t accont for mount points. You would have to know what the mount points are to do that, and that’s another discussion. If you’ve got straight drives though you can use this as-is.

select 
LEFT(physical_name, 2) as Drive
, sum(size*8)/1024.0 as TotalInMB 
, sum(size*8)/1024.0/1024.0 as TotalInGB
from sys.master_files mf
inner join sys.databases sd
on sd.database_id = mf.database_id
where sd.state_desc = 'offline'
group by LEFT(physical_name, 2)