Disk Space Monitoring using SQL Server DMV -

Disk Space Monitoring using SQL Server DMV

DHARMENDRA KESHARIJanuary 09, 2017SQL WINDOWS8 comments

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:

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.

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!

DHARMENDRA KESHARI

8 thoughts on “Disk Space Monitoring using SQL Server DMV”

Rajesh singh June 17, 2017 at 5:19 am

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 June 17, 2017 at 5:34 am
  
  Thanks for your feedback and also giving an idea to write new blog :)! Sure, I will be posting it next two weeks.
  
  Reply
Braj October 13, 2017 at 2:13 pm

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 October 13, 2017 at 11:31 pm
  
  Thanks Braj!
  
  Reply
Jagadish March 30, 2018 at 6:54 am

This is cool stuff

Reply
- Dharmendra March 30, 2018 at 6:58 am
  
  Thank you Jagadish!
  
  Reply
Lohith April 2, 2018 at 9:51 am

Thanks for the sharing! This is really helpful.

Reply
Prashant Shah April 2, 2018 at 10:29 pm

Very Nice Script!

Reply