When you are trying to get information about drives on your server you might see that SQL is returning NULL values instead of actual values. In this article, we will understand the reason for such NULL values and how to prevent those.
Dynamic management function
sys.dm_os_volume_stats is a SQL Server dynamic management function with which you can
- get your server’s volume information
- attributes of server’s drives
- status of free space
This dynamic management function requires two parameters and they are
- Database id
- File id
If you run the code below from a new query window, you can see file info of master database.
select * from sys.dm_os_volume_stats(1, 1);
The result set would look like this. The only drawback is that the space info is returned in bytes. You have to convert that into MB or GB.
This dynamic management function can be combined with the system table sys.master_files to retrieve file size of each database file, the total space and the free space of the drive hosting database files.
SELECT systable1.name, systable1.physical_name, dynamicfunction1.volume_mount_point AS drive, dynamicfunction1.total_bytes AS total_disk_in_bytes, dynamicfunction1.available_bytes AS free_disk_in_bytes FROM sys.master_files systable1 OUTER APPLY sys.dm_os_volume_stats (systable1.[database_id], systable1.[file_id]) dynamicfunction1
This is a good method to monitor disk space on your SQL Server. At times, in the result set, you might see NULL values for disk info. In such cases, windows error log is the first place that you must check to fix this issue. If you search the windows application error log for the string “Access is denied”, you can see entries when the script was run. This indicates that the account with which the script was run does not have the necessary permission to read system information.
If the SQL Server service account is added to the Windows Administrator group, then the query above will start displaying values instead of NULL. The SQL Server service account should have at least read permission on the drive that is hosting the database files. Please also ensure that your SQL Server service account has VIEW SERVER STATE permission.
You can create a log table to capture the result set from the query above. Add this query inside a SQL Server agent job and schedule it as per your requirement to collect and track disk space on your SQL Servers. You can then script out the SQL Server agent job, create Registered Servers using your SQL Server management studio and then run the job script from a new query window. This would deploy the job on all your target servers. You can then create linked servers and read information from each of these SQL Servers. HTML alert emails can be configured using TSQL to alert database administrators about drives that run on very low free space.
It is always a good practice to check your disk health. Because, an unhealthy disk can easily result in sql corruption
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including damaged Outlook and excel recovery software products. For more information visit www.datanumen.com