Take the 2-minute tour ×

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?

share|improve this question
The database choice is under my control so will be SQL Server 2005 or later. –  Ries Feb 11 '10 at 9:08

2 Answers 2

up vote 6 down vote accepted

Something like this?

declare @DatabaseName sysname 
set @DatabaseName = 'master'

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

    mas.type_desc FileType, 
    mas.name FileName, 
    mas.physical_name PhysicalFileName, 
    mas.size * 8 / 1024 FileSizeInMegabytes,
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.

share|improve this answer

You haven't mentioned your version of SQL Server. Starting with SQL Server 2005 you can get plenty of information from sys.database_files and relate that to the output of xp_fixeddrives.

share|improve this answer

Your Answer


