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

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

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

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.
Iterate thought all of the #SubTreeDirs table entries and do a DIR command on each directory inserting the results to a temporary table #tempTB.
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.
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

About the author

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