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

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


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.


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))

   DECLARE @curdir VARCHAR(400)
   DECLARE @line VARCHAR(400)
   DECLARE @command VARCHAR(400)
   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
      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 (
      VALUES (
         ,@filesize / (1024 * 1024)

      SET @cntr -= 1

   FROM #OutReport
   WHERE Directory IS NULL

   SELECT TOP 10 *
   FROM #OutReport

   DROP TABLE #OutReport


   DROP TABLE #SubTreeDirs


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' 

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 
    exec sp_configure 'xp_cmdshell', 1 
  • The procedure was tested on Microsoft SQL Server 2014 and SQL Server 2016

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

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



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

I hope that this procedure will serve you fine.


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


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:

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.

































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

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 (57736)

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 (57453)

 Got the same error.

Replace the line with:

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


Friday, June 2, 2017 - 2:18:11 AM - Laurens Back To Top (56486)

 The Stored procedure returned error:

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


get free sql tips
agree to terms