Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Version - SQL Server 2012 SP1 CU3.

I have been using sys.dm_os_volume_stats for a while and it was working. Recently I have not been able to return any result from this DMF on one SQL Server 2012 instance.

e.g.

SELECT * FROM sys.dm_os_volume_stats(1, 1);

I have no issues with the same code on other SQL Server 2012 or SQL Server 2008 R2 instances. Does anyone know the reason behind and has fixed this problem?

share|improve this question
    
does this still give you no results? ` select d.name, d.type, d.physical_name, vfs.* from sys.master_files d OUTER apply sys.dm_os_volume_stats(d.database_id, d.file_id) vfs;` –  swasheck Nov 18 '13 at 22:03
    
What user are you connected as? Are you sure you have VIEW SERVER STATE permissions? –  Aaron Bertrand Nov 18 '13 at 22:39
    
@swasheck I was using similar type of query like yours and it was returning NULL on columns from this DMF. Issue is that the DMF doesn't return any result. –  Travis Gan Nov 19 '13 at 13:49
    
@AaronBertrand Yes. I was using an account with sysadmin role. –  Travis Gan Nov 19 '13 at 13:50

1 Answer 1

up vote 1 down vote accepted

After some tests, in order for the DMF to work, it appears that the service account needs to have at least READ access to the root volume of where the database files are located at, in addition to the SQL Server login security VIEW SERVER STATE permission.

The READ permission does not have to be granted explicitly to the service account. It could be granted through other user/groups,

1) SQL Server service account
2) Everyone
3) Users
4) NT Authority\Authenticated Users

The idea is SQL Server service account needs at least READ permission to the root volume. I have listed the test and details here.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.