SQL Server: Monitoring Disk Space with FSUTIL command line utility
Introduction
This article talks about one of SQL method to fetch the disk usage details and use of T-SQL in conjunction with invoking FSUTIL utility using SQL Windows Shell interface xp_cmdshell. The FSUTIL FSINFO lists all drives, queries the drive type, queries volume information, queries NTFS-specific volume information, or queries file system statistics. Other SQL Methods to get Disk Space details are OLE and WMI
Pre-requisites
- The FSUTIL utility requires that you have administrative privileges
- sysadmin rights on the SQL server
- Enable xp_cmdshell
Data Flow
The below diagram depicts the flow of the code
Code Details
This section briefs the internal detail of the SQL code and its execution
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
Querying FSUTIL command utility
The below SQL lists drives attached to the file system
EXEC [master].[dbo].[xp_cmdshell] N'FSUTIL FSINFO DRIVES'
The FSUTIL volume diskfree command list the drive usage statistics of the filesystem. The below example gathers the statistics of c drive.
declare @driveName varchar(100)
declare @query varchar(1000)
declare @fsinfo varchar(1000)
set @driveName = 'C'
set @query ='FSUTIL VOLUME DISKFREE '+@driveName+':\'
exec xp_cmdshell @query
The below screen shot shows the output of FSUTIL DRIVES and VOLUME.
Prepare string for XML parsing
In the code, the where clause construct is used to get the non-null value from FSUTIL FSINFO DRIVES output and then manipulated using string function to get only the drive details A\C\D\E\F\G\
/* Inserting the non- null values to temp table */
SELECT @workstring = [output]
FROM @xp_cmdshell_output
WHERE [output] LIKE 'Drives:%'
DELETE FROM @xp_cmdshell_output
/* Prepare string for XML parsing*/
-----------------------------------------------
-- Replace string "Drives",":" and " " by ''
---------------------------------------------
SELECT @workstring = REPLACE(REPLACE(REPLACE(@workstring, 'Drives', ''),':',''),' ','')
--PRINT @workstring
Output : A\C\D\E\F\G\
String splitting using XML
The code uses XML parsing to split the string. The Splitting of delimited strings Using XML is shown below. The Parsed value is stored in a temporary table for further processing. The parsed output will be processed row by row to get a usage statistics of each drive
SELECT @XML = CAST(
('<X>'
+ REPLACE(@workstring
,'\'
,'</X><X>')
+ '</X>')
AS XML)
INSERT INTO @drives ([Drive])
SELECT LTRIM(N.value('.', 'VARCHAR(4000)'))
FROM @XML.nodes('X') AS T(N)
WHERE ASCII(LTRIM(N.value('.', 'VARCHAR(4000)'))) != 0
select * from @drives
String Manipulation to update size and Free space
The next part of the script is to loop through each drive and manipulate the data using string function. The drive detail is fed to FSUTIL VOLUME DISKFREE command and then its output is stored in a temp table variable. Then the output is transformed, manipulated using where clause and string function to fetch a numeric value from string output and then updates corresponding Size columns using conversion formula of the table variable. The above steps are repeated to update the Free columns using the similar conversion formula.
SELECT @recid = 1
WHILE @recid <= (SELECT MAX([RecID]) FROM @drives)
BEGIN
SELECT @workstring = ''
,@vexec_str = 'EXEC [master].[dbo].[xp_cmdshell] '
+ QUOTENAME('FSUTIL VOLUME DISKFREE '
+ [Drive]
+ ':'
,CHAR(39))
FROM @drives
WHERE [RecID] = @recid
INSERT INTO @xp_cmdshell_output ([output])
EXEC (@vexec_str)
SELECT @workstring = [output]
FROM @xp_cmdshell_output
WHERE [output] LIKE '%Total # of bytes%'
IF @workstring IS NOT NULL AND LEN(@workstring) > 0
BEGIN
SELECT @workstring = LTRIM(
SUBSTRING(@workstring
,CHARINDEX(':'
,@workstring
) + 1
,LEN(@workstring)
)
)
SELECT @workstring = LEFT(@workstring, LEN(@workstring))
/* update the free field and convert its value to GB */
UPDATE @drives
SET [Size] = (CONVERT(numeric, @workstring))/1024/1024/1024.00
WHERE [RecID] = @recid
END
ELSE
DELETE
FROM @drives
WHERE [RecID] = @recid
The last part of the code fetches the data from the table variable
SELECT @@ServerName server,Drive,Size,Free, cast(Free/Size * 100.00 as decimal(5,2)) '%Free' FROM @drives
Download
The code is uploaded to the gallery TSQL_DiskSpace_FSUTIL
SQL Code
The complete code is given below. The code has broken into many pieces and explained above with few screen shots.
/* Variable declaration*/
DECLARE @recid INT
,@workstring VARCHAR(8000)
,@XML XML
,@vexec_str VARCHAR(8000)
-- Create table variable to hold drive size info
DECLARE @drives TABLE (
[RecID] TINYINT IDENTITY(1,1) -- Record ID
,[Drive] VARCHAR(10) -- Drive letter
,[Size] NUMERIC NULL -- Drive size
,[Free] NUMERIC NULL
)
-- Create table variable for xp_cmdshell output
DECLARE @xp_cmdshell_output TABLE (
[output] VARCHAR(8000) NULL -- Raw text returned from xp_cmdshell execution
)
INSERT INTO @xp_cmdshell_output ([output])
EXEC [master].[dbo].[xp_cmdshell] N'FSUTIL FSINFO DRIVES'
/* Error handling*/
-----------------------------------------------------------------
--Check for sql server privilege to execute the FSUTIL utility to gather disk status
-----------------------------------------------------------------
IF (SELECT COUNT(1)
FROM @xp_cmdshell_output
WHERE [output] = 'The FSUTIL utility requires that you have administrative privileges.') > 0
RAISERROR ('SQL Server Service account not an admin on this computer.', 11, 1);
ELSE
BEGIN
/* Inserting the non-null values to temp table */
SELECT @workstring = [output]
FROM @xp_cmdshell_output
WHERE [output] LIKE 'Drives:%'
DELETE FROM @xp_cmdshell_output
/* Prepare string for XML parsing*/
-----------------------------------------------
-- Replace string "Drives",":" and " " by ''
---------------------------------------------
SELECT @workstring = REPLACE(REPLACE(REPLACE(@workstring, 'Drives', ''),':',''),' ','')
--PRINT @workstring
END
/* XML Parsing - Splitting the delimited string using XML*/
-----------------------------------------------------
-- the string is parsed for the delimiter '\'
-----------------------------------------------------
SELECT @XML = CAST(
('<X>'
+ REPLACE(@workstring
,'\'
,'</X><X>')
+ '</X>')
AS XML)
/* Store the parsed value into table variable */
INSERT INTO @drives ([Drive])
SELECT LTRIM(N.value('.', 'VARCHAR(4000)'))
FROM @XML.nodes('X') AS T(N)
WHERE ASCII(LTRIM(N.value('.', 'VARCHAR(4000)'))) != 0
----
--Display the results
---
select * from @drives
-- Get size for each drive
SELECT @recid = 1
WHILE @recid <= (SELECT MAX([RecID]) FROM @drives)
BEGIN
SELECT @workstring = ''
,@vexec_str = 'EXEC [master].[dbo].[xp_cmdshell] '
+ QUOTENAME('FSUTIL VOLUME DISKFREE '
+ [Drive]
+ ':'
,CHAR(39))
FROM @drives
WHERE [RecID] = @recid
INSERT INTO @xp_cmdshell_output ([output])
EXEC (@vexec_str)
SELECT @workstring = [output]
FROM @xp_cmdshell_output
WHERE [output] LIKE '%Total # of bytes%'
IF @workstring IS NOT NULL AND LEN(@workstring) > 0
BEGIN
SELECT @workstring = LTRIM(
SUBSTRING(@workstring
,CHARINDEX(':'
,@workstring
) + 1
,LEN(@workstring)
)
)
SELECT @workstring = LEFT(@workstring, LEN(@workstring))
/* update the free field and convert its value to GB */
UPDATE @drives
SET [Size] = (CONVERT(numeric, @workstring))/1024/1024/1024.00
WHERE [RecID] = @recid
END
ELSE
DELETE
FROM @drives
WHERE [RecID] = @recid
SELECT @workstring = [output]
FROM @xp_cmdshell_output
WHERE [output] LIKE '%Total # of free bytes%'
IF @workstring IS NOT NULL AND LEN(@workstring) > 0
BEGIN
SELECT @workstring = LTRIM(
SUBSTRING(@workstring
,CHARINDEX(':'
,@workstring
) + 1
,LEN(@workstring)
)
)
SELECT @workstring = LEFT(@workstring, LEN(@workstring))
/* update the free field and convert its value to GB */
UPDATE @drives
SET [free] = (convert(numeric, @workstring))/1024/1024/1024.00
WHERE [RecID] = @recid
END
ELSE
DELETE
FROM @drives
WHERE [RecID] = @recid
DELETE FROM @xp_cmdshell_output
SELECT @recid = @recid + 1
END
SELECT @@ServerName server,Drive,Size,Free, cast(Free/Size * 100.00 as decimal(5,2)) '%Free' FROM @drives
Output
Conclusion
There are many ways to gather disk space, it's up to an individual to gather the metrics using the available list of tools and utilities. The above steps only brief about the other way of capturing the details.
References
FSUTIL
Community Question
See Also
The below article gives an idea to execute sql script over multiple servers using sqlcmd
- **SQL - Disk Space Monitoring using OLE and WMI **
- **PoSH - DiskSpaceGUITool **