Get drive data for ALL drives in T-SQL

Posted on by David

There are a few methods you can use to get drive information using T-SQL. The problem is all these methods have some limitations. They provide free space but not drive capacity or they only provide data for volumes that contain database files. Ideally Microsoft would provide an easy way to get both drive capacity and free space for all drives. In this article I’ll demonstrate a way to do this using T-SQL.

Here are a few exiting methods that you might consider before we continue:

Get drive information using T-SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- Gets size and free space but only for volumes that have SQL data files.
SELECT DISTINCT vs.volume_mount_point AS DriveLetter,
	vs.total_bytes / POWER(1024.0,3) AS SizeGB,
	vs.available_bytes / POWER(1024.0,3) AS FreeGB,
	vs.logical_volume_name as Label
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs

-- Needs SQL 2019 & free space only
SELECT fixed_drive_path, 
	drive_type_desc, 
	free_space_in_bytes / POWER(1024.0,3) FreeGB
FROM sys.dm_os_enumerate_fixed_drives;

-- Gets free space on drive only
EXEC xp_fixeddrives

If these work for your needs, great! If you need capacity and free space for ALL drives you will need a different solution. The best option might be to query that data outside of SQL Server using PowerShell or your programming/scripting language of choice. If you need to do this in T-SQL though, the script below provides a way.

The script uses xp_cmdshell to run a powershell command to get data for ALL the volumes on your system. Powershell formats this data as XML which we capture in the @output table variable - 1 row per line of XML. Then we convert this back to a single string and store as XML in variable @x. We then shred the XML into a table format. This is a bit of a hack - sys.dm_os_volume_stats is a cleaner method but doesn’t give you data for all drives. As a bonus you get more detailed data about those drives; file system, allocation unit size & more.

Drive information for ALL drives using T-SQL
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

/* 
	Created by David Wiseman
	https://wiseDATAman.com

	Get drive information for all drives via T-SQL.
	This requires access to xp_cmdshell
*/
IF EXISTS(SELECT * FROM sys.configurations
			WHERE name = 'xp_cmdshell'
			AND value_in_use=1
			) AND EXISTS(SELECT * FROM fn_my_permissions ( 'xp_cmdshell', 'OBJECT' ) WHERE permission_name='EXECUTE')
BEGIN
	SET NOCOUNT ON
	DECLARE @x XML
	DECLARE @output TABLE(
		ID INT IDENTITY(1,1) PRIMARY KEY,
		line NVARCHAR(MAX)
	)
	INSERT INTO @output
	EXEC xp_cmdshell 'powershell -noprofile -Command " & { ConvertTo-Xml -As "String" -NoTypeInformation -InputObject (Get-Volume | Select-Object -Property DriveLetter,Size,SizeRemaining,FileSystem,FileSystemLabel,AllocationUnitSize,DriveType,FileSystemType,HealthStatus,OperationalStatus,UniqueId )}"'

	/* Alternative option for SQL 2017+
	SELECT @x = STRING_AGG(line,CHAR(13) + CHAR(10)) WITHIN GROUP (ORDER BY ID) 
	FROM @output
	WHERE ID>1 -- skip encoding line
	*/

	SELECT @x = (SELECT line + CHAR(13) + CHAR(10)
	FROM @output 
	WHERE ID >1 -- skip encoding line
	ORDER BY ID
	FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')

	SELECT T.c.value('(Property[@Name="DriveLetter"])[1]','CHAR(1)')  as DriveLetter,
		T.c.value('(Property[@Name="Size"])[1]','BIGINT')/POWER(1024.0,3)  as SizeGB,
		T.c.value('(Property[@Name="SizeRemaining"])[1]','BIGINT')/POWER(1024.0,3)  as FreeGB,
		T.c.value('(Property[@Name="FileSystem"])[1]','NVARCHAR(256)')  as FileSystem,
		T.c.value('(Property[@Name="FileSystemLabel"])[1]','NVARCHAR(256)')  as FileSystemLabel,
		T.c.value('(Property[@Name="AllocationUnitSize"])[1]','INT')  as AllocationUnitSize,
		T.c.value('(Property[@Name="DriveType"])[1]','NVARCHAR(256)')  as DriveType,
		T.c.value('(Property[@Name="FileSystemType"])[1]','NVARCHAR(256)')  as FileSystemType,
		T.c.value('(Property[@Name="HealthStatus"])[1]','NVARCHAR(256)')  as HealthStatus,
		T.c.value('(Property[@Name="OperationalStatus"])[1]','NVARCHAR(256)')  as OperationalStatus,
		T.c.value('(Property[@Name="UniqueId"])[1]','NVARCHAR(256)')  as UniqueId
	FROM @x.nodes('/Objects/Object/Property') T(c)
END
ELSE
BEGIN
	RAISERROR('xp_cmdshell required',11,1)
END

A similar technique could be used to capture the output of other powershell commands in T-SQL. It might not be a good idea but I think it is an interesting technique to demo.

Posted in T-SQL, SQL Server