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)
