Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Use T-SQL to Find Folders Using the Most Disk Space


By:   |   Read Comments (6)   |   Related Tips: More > Scripts


Share your SQL Server knowledge and make some money too!


Problem

Your SQL Server drives are running out of disk space and you want a way to quickly tell which sub-directories are taking the most space.  In this tip we will look at some T-SQL code that you can use to find potential issues.

Solution

The solution I created is a stored procedure that I have called dbo.Top10FolderSubTreeSizeReport.  The procedure accepts a folder path that is the root of the subtree and looks though each folder to see which folders are using the most disk space.

The Method

  1. Issue a DIR command using xp_cmdshell: DIR c:\root /S/O/B/A:D
    • The /S switch Displays all subdirectories under the specified directory along with all subdirectories.
    • The /O switch Lists by name in a sorted order.
    • The /B switch Uses the bare format (no heading information or summary)
    • The /A:D switch Displays files with the specified attribute set to Directory names. This gives us all the directory names and paths in the sub tree under the starting folder and inserts the data into a temporary table called #SubTreeDirs.
  2. Iterate thought all of the #SubTreeDirs table entries and do a DIR command on each directory inserting the results to a temporary table #tempTB.
  3. Extract the final data were the directory size in bytes is written, translate it to megabytes and insert the result folder name and size into the #OutReport temporary table.
  4. The result of all this is the top ten folders ordered by the folder size in descending order.

Here is the T-SQL script for the stored procedure I used to solve this problem:

CREATE PROCEDURE Top10FolderSubTreeSizeReport (@folderPath VARCHAR(80))
AS
BEGIN
   SET NOCOUNT ON

   DECLARE @curdir VARCHAR(400)
   DECLARE @line VARCHAR(400)
   DECLARE @command VARCHAR(400)
   DECLARE @cntr BIGINT
   DECLARE @filesize BIGINT

   -- Create a table that holds all directory names in sub tree
   CREATE TABLE #SubTreeDirs (
      dir_no BIGINT identity(1, 1)
      ,dirPath VARCHAR(400)
      )

   -- create table that holds all the DIR commands output executed on each directory
   CREATE TABLE #TempTB (textline VARCHAR(400))

   -- create the table that holds the output of directory name and size
   CREATE TABLE #OutReport (
      Directory VARCHAR(400)
      ,FileSizeMB BIGINT
      )

   SET @command = 'dir "' + @folderPath + '"' + ' /S/O/B/A:D'

   INSERT INTO #SubTreeDirs
   EXEC xp_cmdshell @command

   SET @cntr = (
         SELECT count(*)
         FROM #SubTreeDirs
         )

   WHILE @cntr <> 0
   BEGIN
      SET @curdir = (
            SELECT dirPath
            FROM #SubTreeDirs
            WHERE dir_no = @cntr
            )
      SET @command = 'dir "' + @curdir + '"'

      TRUNCATE table #tempTB
      INSERT INTO #tempTB
      EXEC master.dbo.xp_cmdshell @command

      SELECT @line = ltrim(replace(substring(textline, charindex(')', textline) + 1, len(textline)), ',', ''))
      FROM #tempTB
      WHERE textline LIKE '%File(s)%bytes'

      SET @filesize = Replace(@line, ' bytes', '')

      INSERT INTO #OutReport (
         directory
         ,FilesizeMB
         )
      VALUES (
         @curdir
         ,@filesize / (1024 * 1024)
         )

      SET @cntr -= 1
   END

   DELETE
   FROM #OutReport
   WHERE Directory IS NULL

   SELECT TOP 10 *
   FROM #OutReport
   ORDER BY FilesizeMB DESC

   DROP TABLE #OutReport

   DROP TABLE #TempTB

   DROP TABLE #SubTreeDirs

   SET NOCOUNT OFF
END
GO

Example Stored Procedure Use

Using the above stored procedure to find the 10 largest directories under the root folder C:\TEMP, I run the following:

exec Top10FolderSubTreeSizeReport 'C:\TEMP' 
go 

Directory                                              FileSizeMB
=====================================================  =============
C:\Temp\Data                                           30
C:\Temp\CollectIT\log                                  21
C:\Temp\Log                                            5
C:\Temp\CollectIT\ldap                                 3
C:\Temp\PCN70152$SQLEXPRESS\Test                       0
C:\Temp\PCN70152$SQLEXPRESS\TSQL2014                   0
C:\Temp\PCN70152$SQLEXPRESS\AdventureWorks2012\FULL    0
C:\Temp\PCN70152$SQLEXPRESS\DocPoint14\FULL            0
C:\Temp\PCN70152$SQLEXPRESS\master\FULL                0
C:\Temp\PCN70152$SQLEXPRESS\model\FULL                 0
Next Steps
  • You probably don't want to run this on the root drive such as "C:\".  This will take a lot of time to traverse through every folder and file to get the output.
  • Using C:\Temp or C:\Temp\ should give you the same results.
  • You can compile and execute the stored procedure in your master database and use it to find directories that are taking a lot of space.
  • The sp_configure 'xp_cmdshell' option must be on for this procedure to work since it uses the DIR command. To enable xp_cmdshell execute the following statements:
    exec sp_configure 'show advanced options', 1 
    go 
    reconfigure 
    go 
    exec sp_configure 'xp_cmdshell', 1 
    go 
    reconfigure 
    go
    
  • The procedure was tested on Microsoft SQL Server 2014 and SQL Server 2016


Last Update:


signup button

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Sunday, June 25, 2017 - 2:19:42 AM - Eli Leiba Back To Top

 

 Lee,

Thank you for your two very important additions to my procedure.

I hope that this procedure will serve you fine.

Eli


Thursday, June 22, 2017 - 11:17:56 AM - Lee Back To Top

Eli,

Thanks for taking the time to create and share this script. I ran into 2 issues however.

1. It did not consider any files in the root of the filepath. So if you specified C:\TEMP and there were 3 files in C:\TEMP and 4 sub-folders, the files in the sub-folders are used in the size calculations BUT the 3 files in C:\TEMP are ignored.

2. Hidden files are ignored. I found this when the totals from your procedure were way less that was was reported on a drive will only 5 folders. When I calculated the folder sizes manually, the total was the same as yours but xp_fixeddrives reported way more space being used. Then I looked closer and found someone had put a pagefile where it should NEVER be.

I fixed the first issue by adding this line:

INSERT INTO #SubTreeDirs(dirPath) SELECT @folderPath

before this code:

INSERT INTO #SubTreeDirs
EXEC xp_cmdshell @command

 

For the second issue I changed the DOS command from this:

SET @command = 'dir "' + @curdir + '"'

To this:

SET @command = 'dir "' + @curdir + '" /a'

 

Thanks again for your time and talent. This code will be very useful to me.

 

Lee

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


Monday, June 19, 2017 - 9:09:34 AM - Eli Leiba Back To Top

Each dir command (dir directoryname ) output is a list of lines that always ends with a line like this :

NNNN File(s)              NNNN bytes

The procedure find these lines and extracts the NNNN number near the 'bytes' word and converts it to a number in MB units.

 

 


Monday, June 19, 2017 - 8:55:10 AM - Eli Leiba Back To Top

Please note that I've tested my procedure with the following SQL and Windows versions:

 Microsoft SQL Server 2014 - 12.0.2000.8 Intel X86 

Feb 20 2014 19:20:46 

Copyright c Microsoft Corporation

Standard Edition on Windows NT 6.1 X86 Build 7601: Service Pack 1

 


Friday, June 16, 2017 - 12:51:35 AM - Ron Back To Top

 Got the same error.

Replace the line with:

SET @filesize = replace(Replace(@line, ' bytes', ''),'.','')

 


Friday, June 02, 2017 - 2:18:11 AM - Laurens Back To Top

 The Stored procedure returned error:

'Msg 8114, Level 16, State 5, Procedure Top10FolderSubTreeSizeReport, Line 54
Error converting data type varchar to bigint.'

 


Learn more about SQL Server tools