Looking at the way SQL has evolved, there are changes coming up in SQL Server 2017 which would help it run on Windows and Linux platforms. Let us learn about dm_os_enumerate_fixed_drives.
There are some new learnings which I have as I played with SQL Server 2017. I promised to myself to share via this blog.
In earlier version of SQL Server if a DBA needs to find the free space on the drive then he would be using
xp_fixeddrives
The output is as below on my machine, which has only two drives.
drive MB free ----- ----------- C 260245 E 259023
In SQL Server 2017, a new DMV has been introduced which would do the same things. Here is the query.
SELECT fixed_drive_path ,free_space_in_bytes / (1024 * 1024) 'Free Space' ,drive_type_desc FROM sys.dm_os_enumerate_fixed_drives
We can see the output in below image.
As we can see, the output is same as earlier extended procedure. I wished that Microsoft should have added total drive space as well, which is still missing.
Let me know what you think about this blog post. Please leave a comment here. Here is a related post.
- Disk Space Monitoring – Detecting Low Disk Space on Server
- Get a List of Fixed Hard Drive and Free Space on Server
Reference: Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
Hi Pinal
xp_fixeddrives wont show you mount points if you want to check drive space on SQL clusters, not sure if
dm_os_enumerate_fixed_drives does , I don’t have a cluster or servers with mount points to test.
You can get total drive space with this query though
SELECT distinct(volume_mount_point)as Drive , total_bytes/1024/1024/1024 as Total_Drive_Space_In_GB, available_bytes/1024/1024/1024 as Total_Free_Space_In_GB
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point,f.database_id, f.file_id, total_bytes,available_bytes
thank you!
how do we check drive space details in SQL server 2019 in Linux environment.