sqlsql-serversql-server-2005diskdiskspace

Determine available disk size with a sql query

I need to determine the available space on the drive where my database lives. I know about the xp_fixeddrives procedure but how do I get the info for the specific drive where my database resides?

Solution

  • Something like this?

    declare @DatabaseName sysname 
set @DatabaseName = 'master'

declare @Drive table(DriveName char, FreeSpaceInMegabytes int)
insert @Drive execute xp_fixeddrives

select 
    mas.type_desc FileType, 
    mas.name FileName, 
    mas.physical_name PhysicalFileName, 
    mas.size * 8 / 1024 FileSizeInMegabytes,
    drv.DriveName, 
    drv.FreeSpaceInMegabytes
from sys.master_files mas
    left join @Drive drv on
        left(mas.physical_name, 1) = drv.DriveName
where database_id = db_id(@DatabaseName)

    Set @DatabaseName accordingly.