Share via


SQL Server: Monitoring Disk Space with FSUTIL command line utility

↑ Return to Top

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

↑ Return to Top

Pre-requisites

  •   The FSUTIL utility requires that you have administrative privileges
  •   sysadmin rights on the SQL server
  •   Enable xp_cmdshell

↑ Return to Top

Data Flow

The below diagram depicts the flow of the code 

↑ Return to Top

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

↑ Return to Top

Download

The code is uploaded to the gallery TSQL_DiskSpace_FSUTIL

↑ Return to Top

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

↑ Return to Top

Output

 

↑ Return to Top

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.

↑ Return to Top

References

FSUTIL

Community Question

↑ Return to Top

See Also

The below article gives an idea to execute sql script over multiple servers using sqlcmd

↑ Return to Top