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

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

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.

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\

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
 

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 numeric value from string output and then updates corresponding Size columns using conversion formula of 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 available list of tools and utilities. The above steps only briefs about an 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