SQL Server Operations: Extending DMF to Report Disk Space of SQL Server
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
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 BIGINT) AS 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([%Free] as 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 f 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
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] [INT] NULL,
[UsedGB] [INT] NULL,
[FreeGB] [FlOAT] NULL,
[% 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] [INT] NULL,
[UsedGB] [INT] NULL,
[FreeGB] [FlOAT] NULL,
[% 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([%Free] as 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 f 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
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
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"'
Output of Case 1
Output of Case 2
Conclusion
- SQL database drive free space monitoring can be done with ease
- Managing data and log drive consumption can be monitored and alerting mechanism can be placed by configuring mail service
- 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.
- 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
- 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
- MSDN reference: http://msdn.microsoft.com/en-us/library/hh223223.aspx
sql-server-daily-operational-tasks
See Also
- SQL Server: Monitoring Disk Space with FSUTIL command line utility
- SQL Server Operations: Monitoring Disk Space with WMI and OLE Automation Objects
- PoSH & SQL : Monitor and Report Disk Usage Stats - Task Scheduler
- PowerShell and SQL : Monitoring Disk Space - XML and ADO.NET Class Libraries