How to retrieve list of drives or mount points that contain the database files and the free space on those drives

This query returns a list of drives or mount points that contain the database files and the free space on those drives




select DISTINCT
VS.volume_mount_point,vs.available_bytes
from AdventureWorks2014.sys.database_files as df
cross APPLY
sys.dm_os_volume_stats(DB_ID(N'AdventureWorks2014'),df.file_id) as vs

Below Query return File names and paths for all user and system databases on instance 

SELECT DB_NAME([database_id]) AS [Database Name], 

       [file_id], [name], physical_name, [type_desc], state_desc,

   is_percent_growth, growth,

   CONVERT(bigint, growth/128.0) AS [Growth in MB], 

       CONVERT(bigint, size/128.0) AS [Total Size in MB]

FROM sys.master_files WITH (NOLOCK)

ORDER BY DB_NAME([database_id]), [file_id] OPTION (RECOMPILE);

As per Glenn Berry

-- Things to look at:

-- Are data files and log files on different drives?

-- Is everything on the C: drive?

-- Is tempdb on dedicated drives?

-- Is there only one tempdb data file?

-- Are all of the tempdb data files the same size?

-- Are there multiple data files for user databases?

-- Is percent growth enabled for any files (which is bad)?

Volume info for all LUNS that have database files on the current instance

SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, 

vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)],

CONVERT(DECIMAL(18,2), vs.available_bytes/1073741824.0) AS [Available Size (GB)],  

CONVERT(DECIMAL(18,2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %]

FROM sys.master_files AS f WITH (NOLOCK)

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs 

ORDER BY vs.volume_mount_point OPTION (RECOMPILE);

Thanks for Reading..