Handy procedure for finding which folders or files on a given disk (or path) are consuming the most space.
This works by shelling out to DOS and then running DIR /s which means that it requires xp_cmdshell to be enabled. It may not be preferred in a production environment, but you can always enable it, run this utility, then disable it again.
After running this by passing in a drive letter (or path), switch to the Messages pane and copy/paste the resultant script into a new window. You now have every file on that disk (or path) in a table that can be queried. Some sample queries are provided, such as GB/Path, identifying files in the Recycle Bin, and sorting by largest files.
If you want to do analysis on a disk or volume that doesn’t have SQL Server, that’s ok – just run this and pass in the path. It will work as long as SQL Server has access to that path. For example,
Exec Admin.dbo.FilesShow '\\10.0.0.17\g$'
Exec Admin.dbo.FilesShow '\\MachineName\MyFileshare'
A global temp table whose name is comprised of the user’s login is used, allowing for querying after the proc finishes executing. Since the user’s name is part of the tablename, multiple persons can use this simultaneously without interfering with each other.
IF object_id('dbo.FilesShow') Is Not Null
DROP PROC dbo.FilesShow
GO
CREATE PROC dbo.FilesShow
@DriveLetterOrPath varchar(200),
@AppendFiles bit = 0,
@SuppressOutput bit = 0,
@QueryHelp1 varchar(Max)= '' OUTPUT,
@QueryHelp2 varchar(Max)= '' OUTPUT,
@Debug bit = 0
AS
/* DATE AUTHOR REMARKS
1/15/12 PPaiva Initial creation.
DESCRIPTION
Populates a global temp table with file info for all files
on a given drive letter or path.
A global temp table is used so the results can be further
refined.
After running proc, switch to Messages pane to get SQL code
for further analysis.
To query for files on all drives, see proc FilesShowAll.
USAGE
-- Admin.dbo.FilesShow c
Admin.dbo.FilesShow r, 0, 0, '', '', 1
Admin.dbo.FilesShow e
Admin.dbo.FilesShow g, 1
Admin.dbo.FilesShow j
Admin.dbo.FilesShow k
Admin.dbo.FilesShow l
Admin.dbo.FilesShow m
Admin.dbo.FilesShow n
Admin.dbo.FilesShow o
Admin.dbo.FilesShow r
Admin.dbo.FilesShow s
Admin.dbo.FilesShow t
Admin.dbo.FilesShow u
Admin.dbo.FilesShow v
Admin.dbo.FilesShow w
Admin.dbo.FilesShow '\\10.0.0.17\g$'
Admin.dbo.FilesShow '\\MachineName\MyFileshare'
To for files on more than one drive letter, use @AppendFiles.
For example, to include files on drives r, s, and t, run:
Exec Admin.dbo.FilesShow r
Exec Admin.dbo.FilesShow s, 1
Exec Admin.dbo.FilesShow t, 1
DEBUG
xp_fixeddrives
xp_cmdshell 'dir j:\ /s /-C /Ogn'
*/
SET NOCOUNT ON
DECLARE @Cmd varchar(500),
@Drive char(1)
IF Len(@DriveLetterOrPath) = 1
SET @Cmd = 'dir /s /-C /Ogn ' + @DriveLetterOrPath + ':\ '
ELSE
SET @Cmd = 'dir /s /-C /Ogn ' + @DriveLetterOrPath
SET @Drive = Left(@DriveLetterOrPath, 1)
IF UPPER(@Drive) >= 'A' AND @Drive <= 'Z'
SELECT ''
ELSE
SET @Drive = '-'
-- Validate drive letter
CREATE TABLE #Drive (
Drive varchar(1),
MBFree char(50)
)
INSERT INTO #Drive
Exec master..xp_fixeddrives
SELECT *
FROM #Drive
IF Len(@DriveLetterOrPath) = 1
BEGIN
IF Not Exists( SELECT *
FROM #Drive
WHERE Drive = @DriveLetterOrPath
)
BEGIN
SELECT ' No ' + Upper(@DriveLetterOrPath) + ': drive found. Acceptable Drives are:' UNION
SELECT Drive
FROM #Drive
RETURN
END
END
-- Generate global table name
DECLARE @GlobalTableName varchar(200) = '##FilesShow_' + Substring(system_user, CHARINDEX( '\', SYSTEM_USER) + 1, 100),
@s varchar(Max)
IF @Debug = 1
Print 'Global table name = ' + @GlobalTableName
IF @AppendFiles = 0
-- Do not append, therefore need to drop the table
BEGIN
SET @s = 'IF Object_ID(''tempdb.dbo.' + @GlobalTableName + ''') Is Not Null
DROP TABLE ' + @GlobalTableName
IF @Debug = 1
Print @s
Exec(@s)
END
ELSE
-- Append - no need to drop, but need to guard against re-running for the same drive
BEGIN
SET @s = 'IF Object_ID(''tempdb.dbo.' + @GlobalTableName + ''') Is Not Null
DELETE FROM ' + @GlobalTableName + ' WHERE Drive = ''' + @Drive + ''''
IF @Debug = 1
Print @s
Exec(@s)
END
-- Always create the table if it does not exist
SET @s = '
IF Object_ID(''tempdb.dbo.' + @GlobalTableName + ''') Is Null
CREATE TABLE ' + @GlobalTableName + '(
ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
Drive char(1),
FreeGBytesFMT char(15),
SizeBytesFMT char(15),
SizeGBytes decimal(10, 1),
Name varchar(1000),
Date datetime,
PathName varchar(2000),
Path varchar(1000),
FreeMBytes int,
SizeBytes bigint,
DeleteScript varchar(2000),
IsRecycleBin bit DEFAULT 0,
Raw varchar(2000)
)'
IF @Debug = 1
Print @s
Exec(@s)
SET @s = '
INSERT INTO ' + @GlobalTableName + ' (Raw)
Exec xp_cmdshell ''' + @Cmd + '''
-- For debugging
--SELECT --charindex(''dir(s)'', Raw) ''Pos "dir(s)"'',
-- --charindex(''bytes free'', Raw) ''Pos "bytes free"'',
-- Replace(substring(Raw, charindex(''dir(s)'', Raw) + 7, charindex(''bytes free'', Raw) - 7 - charindex(''dir(s)'', Raw)), '','', '''')
--FROM ##FilesShow_PaulLocal
--WHERE Raw Like ''%bytes free%''
UPDATE ' + @GlobalTableName + '
SET FreeMBytes = Convert(bigint,
Replace(substring(Raw, charindex(''dir(s)'', Raw) + 7, charindex(''bytes free'', Raw) - 7 - charindex(''dir(s)'', Raw)), '','', '''')
) /1000000
FROM ##FilesShow_PaulLocal
WHERE Raw Like ''%bytes free%''
UPDATE ' + @GlobalTableName + '
SET FreeMBytes = (SELECT Max(FreeMBytes) FROM ' + @GlobalTableName + ')
DELETE
FROM ' + @GlobalTableName + '
WHERE Left(Raw, 2) = '' ''
OR Left(Raw, 4) = '' Vol''
OR Raw Is Null
OR Raw Like ''%<%''
UPDATE ' + @GlobalTableName + '
SET Date = Convert(datetime, Left(Raw, 20)),
SizeBytes = Convert(bigint, Substring(Raw, 21, 18)),
SizeBytesFMT = Admin.dbo.udfPadLeft(Convert(varchar(30), Admin.dbo.udfFormatInteger(Convert(bigint, Substring(Raw, 21, 18)))), 15, '' ''),
SizeGBytes = Convert(bigint, Substring(Raw, 21, 18)) / 1000000000.0,
Name = Substring(Raw, 40, 1000)
WHERE Left(Raw, 13) <> '' Directory of''
AND Left(Raw, 1) Like ''[0-9]''
UPDATE ' + @GlobalTableName + '
SET Path = Substring(Raw, 15, 1000)
WHERE Left(Raw, 13) = '' Directory of''
'
IF @Debug = 1
Print @s
Exec(@s)
SET @s = 'DECLARE @ID int,
@MaxID int,
@PrevPath varchar(1000),
@Path varchar(1000)
SELECT @MaxID = Max(ID),
@ID = 0
FROM ' + @GlobalTableName + '
WHILE @ID < @MaxID
BEGIN
-- Get Previous Path
SELECT @PrevPath = Path
FROM ' + @GlobalTableName + '
WHERE ID = @ID
-- Get next row
SELECT @ID = Min(ID)
FROM ' + @GlobalTableName + '
WHERE ID > @ID
-- Get corresponding Path
SELECT @Path = Path
FROM ' + @GlobalTableName + '
WHERE ID = @ID
IF @Path Is Null AND @PrevPath Is Not Null
UPDATE ' + @GlobalTableName + '
SET Path = @PrevPath
WHERE ID = @ID
END
'
IF @Debug = 1
Print @s
Exec(@s)
SET @s = '
DELETE
FROM ' + @GlobalTableName + '
WHERE Date Is Null
UPDATE ' + @GlobalTableName + '
SET PathName = dbo.udfAddBackslashIfNec(Path) + Name
UPDATE ' + @GlobalTableName + '
SET DeleteScript = ''Exec xp_cmdshell ''''del "'' + PathName + ''"''''''
UPDATE ' + @GlobalTableName + '
SET IsRecycleBin = 1
WHERE PathName Like ''' + @DriveLetterOrPath + ':\$Recycle.Bin\%''
OR PathName Like ''' + @DriveLetterOrPath + ':\Recycle%''
UPDATE ' + @GlobalTableName + '
SET Drive = ''' + @Drive + '''
-- Total bytes free
UPDATE ' + @GlobalTableName + '
SET FreeGBytesFMT = Admin.dbo.udfFormatInteger((FreeMBytes/1000.))'
IF @Debug = 1
Print @s
IF @Debug = 1
SELECT @GlobalTableName '@GlobalTablename',
@DriveLetterOrPath '@DriveLetterOfPath',
@Drive '@Drive'
Exec(@s)
-- Optional indexes
--SET @s = 'CREATE INDEX idx_Name_' + @GlobalTableName + ' ON ' + @GlobalTableName + '(Name)
--CREATE INDEX idx_SizeBytes_' + @GlobalTableName + ' ON ' + @GlobalTableName + '(SizeBytes)
--CREATE INDEX idx_PathName_' + @GlobalTableName + ' ON ' + @GlobalTableName + '(PathName)
--'
--IF @Debug = 1
-- Print @s
--Exec(@s)
SET @QueryHelp1 = 'SELECT Drive,
Count(*) NumFiles,
Sum(SizeGBytes) UsedGBytes,
Min(FreeMBytes/1000) FreeGBytes
FROM ' + @GlobalTableName + '
GROUP BY Drive
ORDER BY Drive
SELECT *
FROM ' + @GlobalTableName + '
'
IF @SuppressOutput = 0
BEGIN
IF @Debug = 1
Print @QueryHelp1
Exec(@QueryHelp1)
END
SET @QueryHelp2 = '
-- Show all files, sort by largest
SELECT *
FROM ' + @GlobalTableName + '
WHERE Name Like ''%%''
ORDER BY SizeBytes desc
-- Count of all files by Drive
SELECT Drive,
Count(*) NumFiles,
Sum(SizeGBytes) UsedGBytes,
Min(FreeMBytes/1000) FreeGBytes
FROM ' + @GlobalTableName + '
GROUP BY Drive
ORDER BY Drive
-- Count of all files, all drives
SELECT Count(*) NumFilesAllDrives, Sum(SizeGBytes) TotalGBytesAddDrives
FROM ' + @GlobalTableName + '
-- GB/Path
SELECT Path, Convert(Decimal(5, 1), Sum(SizeBytes/1000000000.0)) SizeGB
FROM ' + @GlobalTableName + '
GROUP BY Path
HAVING Sum(SizeBytes/1000000000.0) >= .25
ORDER BY 2 desc
-- Files in Recycle Bin
SELECT *
FROM ' + @GlobalTableName + '
WHERE IsRecycleBin = 1
ORDER BY SizeBytes desc
-- Sum of Files in Recyle Bin
SELECT Admin.dbo.udfFormatInteger(Sum(SizeBytes)) SumBytesInRecycleBin
FROM ' + @GlobalTableName + '
WHERE IsRecycleBin = 1
-- Show most recent files
SELECT *
FROM ' + @GlobalTableName + '
ORDER BY Date desc
'
IF @SuppressOutput = 0
Print @QueryHelp2
