SQL DMF sys.dm_os_volume_stats not returning result

If you familiar with this dynamic management function (DMF) and have been using it, but suddenly this DMF returns no result on some or all of the referring database files, this post discusses discusses the root cause and solution that may help you in your troubleshooting.

SQL Server introduces new dynamic management function (DMF) sys.dm_os_volume_stats since SQL Server 2008 R2. This DMF could be used to returns several attributes of physical disk drive or available free disk space information about the directory.
Here is an example of using this DMF,

SELECT 
 m.name, 
 m.physical_name,
 m.size / 128.0 AS file_size_MB,
 v.volume_mount_point AS drive,
 v.total_bytes / 1048576 AS total_disk_MB,
 v.available_bytes / 1048576 AS free_disk_MB
FROM sys.master_files m
OUTER APPLY sys.dm_os_volume_stats (m.[database_id], m.[file_id]) v

The query shows the drive, total disk space, free disk space from the sys.dm_os_volume_stats DMF. Great. That's what is expected.

Note that the user database 'Test' is purposely move to a different drive, D:\ in this case for demo purpose.

Now, the problem is when we get this as a result,

The DMF returns NULL for Test database files. Please note that the query is using OUTER APPLY. If you are using CROSS APPLY, it does not return the Test database files rows.

What happens?

'Luckily', the Windows application error log shows this error,

The operating system returned the error '5(Access is denied.)' while attempting 'GetVolumeInformation' on '\\?\Volume{f470f87e-52b3-11e3-93ff-806e6f6e6963}\' at 'storagedmv.cpp'(308).

The reason I said 'luckily' is because at least there is some sort of an error giving you indication what might be wrong. Sometimes we are not as lucky like the DBCC CHECKDB internal database snapshot failure.

It appears that that is access issue to the volume. If you referring to volume_id column in the first screen shot, that is the D: drive where test databases are located at.

It appears to be the service account, in this case NT SERVICE\MSSQLSERVER, does not have the access to the root volume (D: drive). It was working before either because the either one of the user/group listed below is granted with at least read permission to the root volume,

1) SQL Server Service Account
2) Everyone
3) NT Authority\Authenticated Users
4) Users (e.g. TestServer\Users). This group usually includes NT Authority\Authenticated Users

Basically the idea is the SQL Server service account needs to has at least read access to the root volume in order for the sys.dm_os_volume_stats to work in addition to SQL Server login security with VIEW SERVER STATE permission.

Back to the example above. You may already guess that the query still returns system database drive information because it is locating on C: drive, in this case there is local server users group granted with at least read permission to the C:. If you remove all the above group from the root volume (C:), the DMF will not return any result.

Is this the reason on DBCC CHECKDB internal database snapshot failure? Read more details here.

This is tested on SQL Server 2008 R2 and SQL Server 2012 on Windows 2008 R2 and Windows 2012.