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

↑ Return to Top


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 updated 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 in 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 privilge 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 - Spilting 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, its 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 

http://social.technet.microsoft.com/wiki/contents/articles/35609.sql-server-operations-monitoring-disk-space-with-wmi-and-ole-automation-objects.aspx

PoSH - DiskSpaceGUITool 

http://social.technet.microsoft.com/wiki/contents/articles/34900.disk-space-gui-tool-multi-server-s-search-grid-and-email-output-powershell.aspx