SQL SERVER – New DMV in SQL Server 2017 – sys.dm_os_enumerate_fixed_drives. A Replacement of xp_fixeddrives

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

Solarwinds
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.

SQL SERVER - New DMV in SQL Server 2017 - sys.dm_os_enumerate_fixed_drives. A Replacement of xp_fixeddrives sql2017-new-dmv

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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – 7 Important Things to Remember While Taking Effective Backup
Next Post
SQL SERVER – Simple Explanation of AT TIME ZONE Feature

Related Posts

2 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

    Reply
  • thank you!

    Reply

Leave a Reply

Menu