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:
|
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.
|
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.