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)