Get disk free space on physical host

Get a list of all volumes on the physical host running SQL Server instance, their allocated/free space in GB, and few more properties. This is an alternative to run EXEC xp_fixedDrives because that won’t work on a Linux-hosted instance.

Note: This script can’t be executed on the Azure SQL database because these two system objects aren’t available there.

	[vs].[volume_mount_point] [Drive], 
	[vs].[file_system_type] [File System Type], 
	[vs].[logical_volume_name] [Logical Volumn 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],  
	CAST(CAST([vs].[available_bytes] AS FLOAT)/ CAST([vs].[total_bytes] AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space_Free_Perc],
	[vs].[supports_compression] [Supports_Compression], 
	[vs].[supports_alternate_streams] [Supports_Alternate_Streams], 
	[vs].[supports_sparse_files] [Supports_Sparse_Files], 
	[vs].[is_read_only] [Is_Read_Only], 
	[vs].[is_compressed] [Is_Compressed]
FROM sys.master_files [mf]
	CROSS APPLY sys.dm_os_volume_stats([database_id], [file_id]) [vs]