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.
- 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
- 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: 2017-06-02
About the author
View all my tips