Abstract

The disks are one of the valuable resources in any database system. It might consists of various files among them the data and log files are critical that hold our data and transactions and that's the reason why we need to keep it available and have enough free space for normal database operations. This article talks about the use of DMF(sys.dm_volume_stats()) in its various versions to report disk usage details of SQL drives

↑ Return to Top



Introduction

The sys.dm_os_volume_stats() Dynamic Management Function (DMF) returns windows operating system volume (directory) on which the sql databases files are stored. It takes two parameters i.e. database_id (ID of the database) and file_id (ID of the database file) and cannot be null. This DMV returns 13 columns.If you’re using SQL Server 2008 R2 with SP1 and above then you could use the new sys.dm_os_volume_stats() DMF. The function sys.dm_os_volume_stats contains information of each data file of every database of an instance. We need to aggregate that information to get the required details of each drive.The following sql uses distinct and CROSS APPLY operator to get the required result. The CROSS APPLY operator returns only those rows which have matching rows in another table

This article has two cases. The first case consists of few columns and drives space of sql data and the log file is measured in GB. It's a pretty straightforward and simple to understand.  The second case consists of few more extra columns than case 1. It includes the total database size of each drive, other files size of SQL Drive and its measured in MB. 

Pre-requisites

  • SQL Server 2008 R2 with SP1 or above
  • Requires VIEW SERVER STATE permission on the server


Data Flow


Details

The first section illustrates the simple use cases to fetch desired results  for various use cases

Volume Information of all the databases


SELECT
    DB_NAME(fs.database_id) as DatabaseName,
    fs.name as FileName,
    volume_mount_point,
    volume_id,
    logical_volume_name,
    total_bytes,
    available_bytes,
    (available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100 '% Used '
FROM
sys.master_files fs
    CROSS APPLY sys.dm_os_volume_stats(fs.database_id,fs.file_id) vs
ORDER BY fs.database_id desc

Estimate the log consumption of log drive of the current database


SELECT
    CAST(CAST(available_bytes AS DECIMAL) / (1024 * 1024 * 1024) AS BIGINTAS gb_free
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
WHERE f.database_id = DB_ID() and
 f.type_desc = 'LOG';


Drive details in MB


 
select distinct
convert(varchar(512), vs.volume_mount_point) as VolumeMountPoint
convert(varchar(512), vs.logical_volume_name) as VolumeName
convert(bigint, round(((vs.total_bytes / 1024.0)/1024.0),0)) as TotalMB
convert(bigint, round((((vs.total_bytes - vs.available_bytes) / 1024.0)/1024.0),0)) as UsedMB
convert(bigint, round(((vs.available_bytes / 1024.0)/1024.0),0)) as FreeMB
convert(decimal(18,1), round(((convert(float, vs.available_bytes) / convert(float, vs.total_bytes)) * 100),1)) as [% Free]
from sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) as vs
 

Drive details in GB


SELECT
    @@SERVERNAME Server,
    Volume,
    CAST(SizeGB as DECIMAL(10,2)) CapacityGB,
    CAST((SizeGB - FreeGB) as DECIMAL(10,2)) UsedGB,
    CAST(FreeGB as DECIMAL(10,2)) FreeGB,
    CAST([%Freeas DECIMAL(10,2))[%Free]
    FROM(
        SELECT distinct(volume_mount_point) Volume, 
          (total_bytes/1048576)/1024.00 as SizeGB, 
          (available_bytes/1048576)/1024.00 as FreeGB,
          (select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as '%Free'
        FROM sys.master_files AS CROSS APPLY 
          sys.dm_os_volume_stats(f.database_id, f.file_id)
        group by volume_mount_point, total_bytes/1048576, 
          available_bytes/1048576
  )T

Step by Step details to fetch disk space across multiple server(s)


The section describes the requirement and briefs about every configuration required for successful execution of the code. The major concern is xp_cmdshell on the centralized server. There is a risk of exposing SQL server to a threat. The workaround is to add the little piece of configuring and de-configure sql statement in the sql file i.e. is enable the parameter configuration value in the beginning of the code and disable it at the end.

Pre-requisites

We can check the disk space by executing a T-SQL script using xp_cmdshell from SSMS. In order to do this, you need to make sure that xp_cmdshell is enabled on the SQL instance. You can execute the below script to check and enable xp_cmdshell . To enable xp_cmdshell you must have at least the ALTER SETTINGS server-level permission. 

  • Enable xp_cmdshell on Centralized Server
  • Shared path for sql file
  • Share path for output file to prepare insert statement
  • Requires membership in the sysadmin fixed server role.

Step By Step Details



The below are the steps to store the data into a central repository

  • Enable xp_cmdshell 
  • List all Servers in c:\Server.txt
  • Table Creation [TLOG_SpaceUsageDetails]
  • save T-SQL script in SpaceCheck.sql
  • Execute dynamic sqlcmd from SSMS
  • select the output by querying TLOG_SpaceUsageDetails

Enable xp_cmdshell 

The xp_cmdshell option is a SQL Server server configuration option that enables system administrators to control whether the extended stored procedure can be executed on a system also this procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdShell', 1;
GO
RECONFIGURE;
GO


List servers in text file

Lists all the servers in a text file c:\server.txt and enable the OLE automation across all the servers. The other way to get around this problem is to include the sp_configure commands in the SQL file and take it out after it is been executed across all the servers

ABC
DEF
EFG

↑ Return to Top



Case 1


The below code details the step that you need to execute to fetch the desired result. The output is relatively simple and straight forward. The sql file is executed to prepare the insert statement and execute the generated statement on a centralized table. The more in depth details are discussed in case 2 section.

Create SQL Table 

Create TLOG_SpaceUsageDetails on the centralized server

CREATE TABLE [dbo].[TLOG_SpaceUsageDetails](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ServerName] [VARCHAR](100) NULL,
    [LogDate] [VARCHAR](10) DEFAULT (CONVERT([varchar](10),getdate(),(112))),
    [Volume] [CHAR](3) NULL,
    [CapacityGB] [INTNULL,
    [UsedGB] [INTNULL,
    [FreeGB] [FlOATNULL,
    [% Free] [FLOAT])

Create SQL File

Save the below content to SQL file and place it on the shared path so that sqlcmd can read the file while traversing across listed servers. for example, the content is saved under Spacecheck.sql on \\abcd\hq\. The full path of the file is going to be \\abcd\hq\spacecheck.sql

  DECLARE
    @RowId_1 INT,
    @LoopStatus_1 SMALLINT,
    @TotalSpace VARCHAR(10),
    @drive varchar(4),
    @TotalSize varchar(10),
    @FreeSpace varchar(10),
    @used varchar(10),
    @percentageOfFreeSpace varchar(10),
    @DML nvarchar(4000)
       
SET NOCOUNT ON
------------------------------------------------------
--Table to Store Drive related information
------------------------------------------------------
CREATE TABLE #drives
 (
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ServerName] [VARCHAR](100) NULL,
    [Volume] [CHAR](3) NULL,
    [CapacityGB] [INTNULL,
    [UsedGB] [INTNULL,
    [FreeGB] [FlOATNULL,
    [% Free] [FLOAT]
 )
    
    
------------------------------------------------------
--Inserting the output of sys.dm_os_volume_stats to ##drives Table
------------------------------------------------------
INSERT INTO #drives(ServerName,Volume,CapacityGB,UsedGB,FreeGB,[% Free])
SELECT
    @@SERVERNAME Server,
    Volume,
    CAST(SizeGB as DECIMAL(10,2)) CapacityGB,
    CAST((SizeGB - FreeGB) as DECIMAL(10,2)) UsedGB,
    CAST(FreeGB as DECIMAL(10,2)) FreeGB,
    CAST([%Freeas DECIMAL(10,2))[%Free]
    FROM(
        SELECT distinct(volume_mount_point) Volume, 
          (total_bytes/1048576)/1024.00 as SizeGB, 
          (available_bytes/1048576)/1024.00 as FreeGB,
          (select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as '%Free'
        FROM sys.master_files AS CROSS APPLY 
          sys.dm_os_volume_stats(f.database_id, f.file_id)
        group by volume_mount_point, total_bytes/1048576, 
          available_bytes/1048576
  )T
 
  select from #drives
    
------------------------------------------------------
--Initialize counters
------------------------------------------------------
 
 SET @RowId_1 = 1
 SET @LoopStatus_1 = 1
    
------------------------------------------------------
--To prepare insert statement
------------------------------------------------------
WHILE (@LoopStatus_1 <> 0) BEGIN
    
SET @DML=''
 
------------------------------------------------------
--Data conversion and manipulation by iterating over all the rows one by one
------------------------------------------------------
 
SELECT
 @drive=volume,
 @FreeSpace=FreeGB,
 @TotalSize=CapacityGB,
 @used=UsedGB,
 @percentageOfFreeSpace=[% Free]
 FROM
 #drives
 WHERE
 ( ID = @RowId_1 )
    
 IF ( @@ROWCOUNT = 0 )
 BEGIN
    SET @LoopStatus_1 = 0
 END
 ELSE
BEGIN
     SET @DML=@DML+ 'insert into TLOG_SpaceUsageDetails(ServerName,Volume,CapacityGB,UsedGB,FreeGB,[% Free])values('+''''+@@servername+''''+','+''''+@drive+''''+','+''''+@TotalSize+''''+','+''''+@used+''''+','+''''+@FreeSpace+''''+','+''''+@percentageOfFreeSpace+'''' +')'
END
PRINT @DML
 SET @RowId_1 = @RowId_1 + 1
    
END
   
   
drop table #drives

↑ Return to Top



Case 2


The section outlines similar output columns of case 1 and it also includes total databases size and other files details which reside on the drive. The in-depth information helps the DBA to proactively address the space issue with much lesser time. The sql file is executed to prepare the insert statement and execute the generated statement on a centralized table. The more in depth details are discussed in case 2 section.

Create SQL Table 

Create TLOG_DBSpaceDetails on the centralized server

CREATE TABLE TLOG_DBSpaceDetails(
    [ServerName] [nvarchar](128) NULL,
    [Volume] [nvarchar](1) NULL,
    [CapacityMB] [bigint] NULL,
    [FreeMB] [bigint] NULL,
    [UsedMB] [bigint] NULL,
    [Free %] [decimal](18, 2) NULL,
    [Used %] [decimal](18, 2) NULL,
    [DBSizeMB] [int] NULL,
    [OtherFilesSizeMB] [bigint] NULL,
    [DBSize %] [decimal](18, 2) NULL,
    [OtherFilesSize %] [decimal](18, 2) NULL,
    logdate varchar(10)
)

Create SQL File

Save the below content to SQL file and place it on the shared path so that sqlcmd can read the file while traversing across listed servers. for example, the content is saved under Spacecheck.sql on \\abcd\hq\. The full path of the file is going to be \\abcd\hq\spacecheck.sql
-----------------------------------------------------
--Variable declaration
----------------------------------------------------
DECLARE
    @RowId_1 INT,
    @LoopStatus_1 SMALLINT,
    @TotalSpace VARCHAR(10),
    @drive varchar(4),
    @TotalSize varchar(10),
    @FreeSpace varchar(10),
    @used varchar(10),
    @percentageOfFreeSpace varchar(10),
    @PercentageUsedSpace varchar(10),
    @DbsSizeMB varchar(10),
    @OtherFilesSizeMB varchar(10),
    @PercentageDBSize varchar(10),
    @PercentageOtherFileSize varchar(10),
    @Logdate varchar(10),
    @DML nvarchar(4000)
       
SET NOCOUNT ON
------------------------------------------------------
--Table to Store Drive related information
------------------------------------------------------
    
CREATE TABLE #drives(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ServerName] [nvarchar](128) NULL,
    [Volume] [nvarchar](1) NULL,
    [CapacityMB] [bigint] NULL,
    [FreeMB] [bigint] NULL,
    [UsedMB] [bigint] NULL,
    [Free %] [decimal](18, 2) NULL,
    [Used %] [decimal](18, 2) NULL,
    [DBSizeMB] [int] NULL,
    [OtherFilesSizeMB] [bigint] NULL,
    [DBSize %] [decimal](18, 2) NULL,
    [OtherFilesSize %] [decimal](18, 2) NULL,
    logdate varchar(10)
)
------------------------------------------------------
--Querying sys.dm_os_volume_stats() and catalog view sys.master_files and inserting the result set into a temp tables.
------------------------------------------------------
 
SELECT DISTINCT SUBSTRING(volume_mount_point,1,1) AS [Drive], total_bytes/1048576 AS [Total_MB], available_bytes/1048576 AS [Available_MB],
(total_bytes/1048576)-(available_bytes/1048576) AS [Used_MB],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),available_bytes/1048576)/CONVERT(DECIMAL(18,2),total_bytes/1048576)*100 )AS [Available_Space_%],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),(total_bytes/1048576)-(available_bytes/1048576))/CONVERT(DECIMAL(18,2),total_bytes/1048576)*100 )AS [Used_Space_%]
INTO #SpaceUsed
FROM sys.master_files F cross apply sys.dm_os_volume_stats (F.database_id,F.file_id) V
ORDER BY SUBSTRING(volume_mount_point,1,1)
------------------------------------------------------
--Getting the used space by databases for each drive into a common table expression “CTE”.
------------------------------------------------------
 
;WITH dbs_size (dbs_size_MB,Drive) AS(
SELECT SUM((size * 8)/1024)AS [dbs_size_MB],SUBSTRING(physical_name,1,1) AS [Drive]
FROM sys.master_files GROUP BY SUBSTRING(physical_name,1,1))
 
    
------------------------------------------------------
--Inserting the output of sys.dm_os_volume_stats to ##drives Table
------------------------------------------------------
INSERT INTO #drives(
ServerName,
Volume,
CapacityMB,
FreeMB,
UsedMB,
[Free %],
[Used %],
DBSizeMB,
OtherFilesSizeMB,
[DBSize %],
[OtherFilesSize %],
Logdate
)
------------------------------------------------------
--Querying the temp table #SpaceUsed and the common table expression dbs_size and adding the current date
------------------------------------------------------
SELECT @@ServerName ServerName,S.Drive Volume, S.Total_MB CapacityMB, S.Available_MB FreeMB, S.Used_MB UsedMB, S.[Available_Space_%] [Free %] ,S.[Used_Space_%] [Used %],D.DBs_Size_MB DBSizeMB,
S.Used_MB-D.DBs_Size_MB AS [OtherFilesSizeMB],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),D.DBs_Size_MB)/CONVERT(DECIMAL(18,2),S.Used_MB)*100) AS [DBSize %],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),(S.Used_MB-D.DBs_Size_MB))/CONVERT(DECIMAL(18,2),S.Used_MB)*100) AS [OtherFilesSize %],
CONVERT([varchar](10),getdate(),(112)) [LogDate]
FROM #SpaceUsed S join dbs_size D ON S.Drive=D.Drive
 
select * from #drives
    
------------------------------------------------------
--Initialize counters
------------------------------------------------------
 
 SET @RowId_1 = 1
 SET @LoopStatus_1 = 1
    
------------------------------------------------------
--To prepare insert statement
------------------------------------------------------
WHILE (@LoopStatus_1 <> 0) BEGIN
    
SET @DML=''
 
------------------------------------------------------
--Data conversion and manipulation by iterating over all the rows one by one
------------------------------------------------------
 
SELECT
 @drive=volume,
 @FreeSpace=FreeMB,
 @TotalSize=CapacityMB,
 @used=UsedMB,
 @percentageOfFreeSpace=[Free %],
 @PercentageUsedSpace=[Used %],
 @DbsSizeMB=DBSizeMB,
 @OtherFilesSizeMB=OtherFilesSizeMB,
 @PercentageDBSize=[DBSize %],
 @PercentageOtherFileSize=[OtherFilesSize %],
 @Logdate=LogDate
 FROM
 #drives
 WHERE
 ( ID = @RowId_1 )
    
 IF ( @@ROWCOUNT = 0 )
 BEGIN
    SET @LoopStatus_1 = 0
 END
 ELSE
BEGIN
----------------------------------------------------
--prepare SQL String for data manipulation
----------------------------------------------------
     SET @DML=@DML+ 'insert into TLOG_DBSpaceDetails(ServerName,Volume,CapacityMB,FreeMB,UsedMB,[Free %],[Used %],DBSizeMB,OtherFilesSizeMB,[DBSize %],[OtherFilesSize %],LogDate)values('+''''+@@servername+''''+','+''''+@drive+''''+','+''''+@TotalSize+''''+','+''''+@used+''''+','+''''+@FreeSpace+''''+','+''''+@percentageOfFreeSpace+''''+','+''''+@PercentageUsedSpace+''''+','+''''+@DbsSizeMB+''''+','+''''+@OtherFilesSizeMB+''''+','+''''+@PercentageDBSize+''''+','+''''+@PercentageOtherFileSize+''''+','+''''+@Logdate+'''' +')'
END
PRINT @DML
 SET @RowId_1 = @RowId_1 + 1
    
END
-----------------------------------
--Dropping the temp table #SpaceUsed. 
------------------------------------
 
DROP TABLE #drives
DROP TABLE #SpaceUsed

↑ Return to Top




Execute SQL


Make sure SQL file and output path is a shared path. We are going to write a concatenated output to spaceDetails.sql

The below three parameters are important to loop through all the listed servers

  • Input server list
  • Shared path where you can place query file
  • Shared output path, the prepared insert statement are written into the file
MASTER..XP_CMDSHELL 'for /f %j in ( f:\servers.txt) do sqlcmd -S %j -i "\\share\hq\SpaceCheck.sql" -E >> "\\share\hq\SpaceDetails.sql"'
GO
MASTER..XP_CMDSHELL 'sqlcmd -S ABCD -i "\\share\hq\SpaceDetails.sql"'

↑ Return to Top


Output of Case 1




Output of Case 2




↑ Return to Top




Conclusion


  1. SQL database drive free space monitoring can be done with ease
  2. Managing data and log drive consumption can be monitored and alerting mechanism can be placed by configuring mail service
  3. It also gives an advantage of measuring non-database files of the drive. Two cases help us in deciding what we need as an output. If we are thinking of simple drive usage details we can go for case 1. If we think of getting the stats for non-database files, think of case 2.
  4. If in a case of using SQL Server 2008 or older? We can think of using xp_fixeddrives or simulating the same process using PowerShell
  5. Customize the query to send an alert to the respective team when the percentage of free space reach a threshold.

References

sys.dm_os_volume_stats

See Also


  1. SQL Server: Monitoring Disk Space with FSUTIL command line utility
  2. SQL Server Operations: Monitoring Disk Space with WMI and OLE Automation Objects
  3. PoSH & SQL : Monitor and Report Disk Usage Stats - Task Scheduler
  4. PowerShell and SQL : Monitoring Disk Space - XML and ADO.NET Class Libraries

↑ Return to Top