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!

The post Disk Space Monitoring using SQL Server DMV appeared first on .