Disk Space Monitoring using SQL Server DMV

DMV : sys.dm_os_volume_stats

Being a SQL Server database administrator, you may often receive alerts or asked by Senior DBAs to check, if the disk space is running out of space on SQL Server.

Before SQL Server 2008 R2 SP1, the best way to check on available disk space from within SQL Server is to use the undocumented xp_fixeddrives. But from SQL Server 2008 R2 SP1 introduces a really cool Dynamic Management Function (sys.dm_os_volume_stats) that exposes several attributes of the physical disk drives that contain your database files. You can run the below script to get the disk utilization detail by SQL Server.

SELECT DISTINCT 
		volume_mount_point [Disk Mount Point], 
		file_system_type [File System Type], 
		logical_volume_name as [Logical Drive Name], 
		CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in GB], ---1GB = 1073741824 bytes
		CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available Size in GB],  
		CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %] 
FROM sys.master_files 
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)

The output of the above query:

Physical-disk-space-details

To make your job easier, create a stored procedure to retrieve the disk usage details 

Instead of searching the command every time to get the disk usage, you can follow the below script to create a stored procedure inside your choice of the database to retrieve the disk information.

USE <DATABASENAME>
GO
CREATE PROCEDURE dbo.disk_Utilized_by_sqlserver
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT 
		volume_mount_point [Disk Mount Point], 
		file_system_type [File System Type], 
		logical_volume_name as [Logical Drive Name], 
		CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Total Size in GB], ---1GB = 1073741824 bytes
		CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Available Size in GB],  
		CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %] 
FROM sys.master_files 
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id)
END
GO

Let’s run the stored procedure to get the disk details.

Physical-disk-details-by-SQLServer

As I mentioned, the “sys.dm_os_volume_stats” DMV exposes several attributes. You can refer the below MSDN link to get more information on the DMV.

 

 

Reference: http://msdn.microsoft.com/en-us/library/hh223223(v=sql.105).aspx

Thanks!

8 thoughts on “Disk Space Monitoring using SQL Server DMV

  • Rajesh singh

    This is awesome stuff. Thanks for sharing it! it will make my daily tasks pretty easy. would you be able to share the a script how to check the disk I/O latency using the DMV?

    Reply
    • Dharmendra Keshari

      Thanks for your feedback and also giving an idea to write new blog :)! Sure, I will be posting it next two weeks.

      Reply
  • Braj

    Hi Dharmendra ,

    Its one of the best script and very useful in DBA’s day to day life.

    Thanks very much sharing your skills and expertise. you are brilliant!

    Thanks

    Braj Bhooshnan

    Reply
    • Dharmendra Keshari

      Thanks Braj!

      Reply
  • Jagadish

    This is cool stuff

    Reply
    • Dharmendra

      Thank you Jagadish!

      Reply
  • Lohith

    Thanks for the sharing! This is really helpful.

    Reply
  • Prashant Shah

    Very Nice Script!

    Reply

Leave a comment

Your email address will not be published. Required fields are marked *