Host server - Disk drives size

Compatible from SQL Server 2008

Returns information about space on disk drives used by databases in instance. This c an be handful in automation for example before creating or restoring new database. Also can be used in some type of reporting when collecting information from more SQL Server instances etc.

IF OBJECT_ID('sys.dm_os_volume_stats') IS NOT NULL
	vs.volume_mount_point AS VolumeMountPoint, 
	vs.file_system_type AS FileSystem, 
	vs.logical_volume_name AS LogicalVolumeName, 
	CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS TotalGB,
	CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS UsedGB,  
	CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS UsedSpacePct
FROM sys.master_files AS f WITH (NOLOCK)	
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs OPTION (RECOMPILE);


Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
VolumeMountPointnvarchar(512)Mount point at which the volume is rooted. Can return an empty string.
FileSystemnvarchar(512)Type of file system volume (for example, NTFS, FAT, RAW). Can return an empty string
LogicalVolumeNamenvarchar(512)Logical volume name. Can return an empty string
TotalGBdecimal(18,2)Total size in GB of the volume. Cannot be null.
UsedGBdecimal(18,2)Available free space in GB on the volume. Cannot be null.
UnusedSpacePctdecimal(18,2)Percent of space in use on the volume.

Leave a Reply