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
Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant. With 19 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at: iecdba@hotmail.com.
- MSSQLTips Awards: Rising Star (50+ tips) – 2019 | Author of the Year Contender – 2016-2019