About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Sunday, January 15, 2012

FilesShow

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