xp_fixeddrives ignores SAN mount points

Recently I was working with a customer on a drive space issue and the disk space alert function failed to alert DBAs about this on time. I was just amused why the alert didn’t work at all. After some research it turned out that the customer had recently added a mount points (SAN) and xp_fixeddrives was not able to detect the SAN mount points as drives and I came across the Microsoft Connect item which stated that this is designed behaviour and not an issue at all 🙂 http://connect.microsoft.com/SQLServer/feedback/details/301832/listing-mount-point-information-from-within-sql-server

Looking at DMVs sys.dm_os_volume_stats returns the drive information, however the parent drive information is being returned for mount points too. So the option left were to use either a WMI or powershell script with xp_cmdshell or implementing a custom solution using a CLR function Here is how it could be done using WMI, This would return the drive spaces for mount points as well.

Here is how we can get the drive information using WMI script

EXEC xp_cmdshell 'wmic volume where drivetype="3" get caption, freespace, capacity, label'

Getting the information using powershell seems easier

EXEC xp_cmdshell 'Powershell.exe "Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"| Format-Table DeviceID, FreeSpace, Size"'  ;

You can find the link for solution using CLR in the reference

References
An enhanced xp_fixeddrives for SQL Server
T-SQL to Get Drive Space Info
CLR stored procedure to get disk space information