Determine available disk size with a sql query

Question

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?

Humorist

The database choice is under my control so will be SQL Server 2005 or later. – Humorist

Answer 1

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.

Hallvard

Answer 2

    SELECT Drive
    ,   TotalSpaceGB
    ,   FreeSpaceGB
    ,   PctFree
    ,   PctFreeExact
    FROM
    (SELECT DISTINCT
        SUBSTRING(dovs.volume_mount_point, 1, 10) AS Drive
    ,   CONVERT(INT, dovs.total_bytes / 1024.0 / 1024.0 / 1024.0) AS TotalSpaceGB
    ,   CONVERT(INT, dovs.available_bytes / 1048576.0) / 1024 AS FreeSpaceGB
    ,   CAST(ROUND(( CONVERT(FLOAT, dovs.available_bytes / 1048576.0) / CONVERT(FLOAT, dovs.total_bytes / 1024.0 /
                         1024.0) * 100 ), 2) AS NVARCHAR(50)) + '%' AS PctFree
    ,   CONVERT(FLOAT, dovs.available_bytes / 1048576.0) / CONVERT(FLOAT, dovs.total_bytes / 1024.0 / 1024.0) * 100 AS PctFreeExact                
    FROM    sys.master_files AS mf
    CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS dovs) AS DE

Bart

Consider adding information on your steps to solve this. – Lobate

Answer 3

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.

Prude

Answer 4

sys.dm_os_volume_stats apparently was introduced in SQL Server 2008 R2, so won't be available before that.

Assuming your database name is master and all the files for the database are on the same volume:

select available_bytes from sys.dm_os_volume_stats(DB_ID('master'), 1)

If they're on different drives, change the file ID (second parameter for the function) to get the other drive's space.

sys.dm_os_volume_stats also has a total_bytes column, which may be useful for calculating percent free.

total_bytes and available_bytes report the volume information, so only one query is needed if all the files reside on the same volume.

Kier