About Me

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 for 11 years, from January 2009 to 2020.

Monday, March 21, 2016

FilesShow (dependencies included)

This utility uses T-SQL to analyze a disk or path to identify where the largest files are, or where the folder is containing the most space.

It does require that xp_cmdshell is turned ON.  You can always turn it back OFF when you are finished.
Exec sp_configure 'show advanced options', 1
reconfigure
Exec sp_configure 'xp_cmdshell', 1

reconfigure

USAGE
After running something like one of these (below), then switch to the Messages tab.
Admin.dbo.FilesShow c
Admin.dbo.FilesShow 'C:\SSIS'


Copy/paste all the text from the Messages tab into a new query window.  You now have some sample queries to get you started.  I find GB/Path helpful.

HOW IT WORKS
Uses xp_cmdshell to run "dir /s" and places all the results into a global temp table which is then scrubbed into a usable form.  The global temp table name is unique - anyone can run this same utility elsewhere and simultaneously without a conflict with table names.

NOTE
All dependencies are included below for these objects:
    udfFormatInteger()
    udfAddBackslashIfNec()
    udfPadLeft()


Use Admin
IF Object_ID('dbo.udfFormatInteger') Is Not Null
       DROP FUNCTION dbo.udfFormatInteger
go

CREATE FUNCTION dbo.udfFormatInteger(
       @In bigint)
RETURNS varchar(20)
AS
/*    DATE              AUTHOR            REMARKS   
      8/19/15           PPaiva            Initial creation.
    
      DESCRIPTION
            Formats a given integer with commas.
                  Examples:
                              IN                OUT
                              123               123
                            12345            12,345
                           -54321           -54,321

      USAGE
            SELECT dbo.udfFormatInteger(1234567890)
            SELECT dbo.udfFormatInteger(12345)
            SELECT dbo.udfFormatInteger(-54321)
            SELECT dbo.udfFormatInteger(0)
*/

BEGIN
      DECLARE @Out varchar(20),
                  @sIn varchar(20),
                  @Balance varchar(20),
                  @CurrTextLen smallint,
                  @IsNegative bit

      IF @In < 0
            BEGIN
                  SET @IsNegative = 1
                  SET @In = Abs(@In)
            END

      SET @sIn = Convert(varchar, @In)
      SET @CurrTextLen = Len(@sIn)
      SET @Out = ''
      SET @Balance = @sIn

      IF @CurrTextLen > 3
            BEGIN
                  WHILE 1 = 1
                        BEGIN
                              SET @Out = ',' + Right(@Balance, 3) + @Out
                              SET @Balance = Substring(@sIn, 1, @CurrTextLen - 3)

                              SET @CurrTextLen = Len(@Balance)
                              IF @CurrTextLen > 3
                                    CONTINUE
                              ELSE
                                    BEGIN
                                          SET @Out = @Balance + @Out
                                          BREAK
                                    END 
                        END
            END

      ELSE
            SET @Out = @sIn

      IF @IsNegative = 1
            SET @Out = '-' + @Out

      RETURN @Out

END

go
IF Object_ID('dbo.udfAddBackslashIfNec') Is Not Null
       DROP FUNCTION dbo.udfAddBackslashIfNec
GO

CREATE FUNCTION dbo.udfAddBackslashIfNec(
       @In varchar(500)
       )
RETURNS varchar(501)
AS
/*     DATE          AUTHOR        REMARKS
       8/19/15              PPaiva        Initial creation.

       USAGE
              SELECT dbo.udfAddBackslashIfNec('C:\blahblah')
              SELECT dbo.udfAddBackslashIfNec('C:\blahblah\')

       DESCRIPTION
              Returns @In with a backslash at the right, if necessary.

*/

BEGIN
       DECLARE @LenIn int,
                     @Out varchar(201)
      
       SET @LenIn = Len(@In)

       IF Right(@In, 1) = '\'  OR   @In = ''
              SET @Out = @In
       ELSE
              SET @Out = @In + '\'
      
      

       RETURN @Out

END


go
IF Object_ID('dbo.udfPadLeft') Is Not Null
       DROP FUNCTION dbo.udfPadLeft
go
CREATE FUNCTION dbo.udfPadLeft(
      @In varchar(100),
      @Width int,
      @PadChar varchar(1)
      )
RETURNS varchar(100)
AS
/*  DATE        AUTHOR            REMARKS
    8/19/15     PPaiva            Initial creation.
    
       DESCRIPTION
              Pads @In with @PadChar so that the length of the returned
              value is @Width.  If the length of @In is greater than @Width
              then @In is returned.

       USAGE
              SELECT dbo.udfPadLeft('7', '3', '0')

*/
BEGIN
      DECLARE @Out varchar(100),
                  @LenIn int


      SET @LenIn = Len(@In)
    
      IF @Width - @LenIn < 0
            SET @Out = @In
      ELSE
            SET @Out = REPLICATE(@PadChar, @Width - @LenIn) + @In

      RETURN @Out

END
go

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,
       @GlobalTableName varchar(200) = '' OUTPUT,
       @Debug bit = 0
AS
/*     DATE          AUTHOR        REMARKS
       2/28/16              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 'C:\Paul'
              Admin.dbo.FilesShow '\\data3\backup\SQLBackup\DB2'

              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

       DEPENDENCIES
              udfFormatInteger()
              udfAddBackslashIfNec()
              udfPadLeft()

       DEBUG
              xp_fixeddrives
              xp_cmdshell 'dir j:\ /s /-C /Ogn'

              SELECT Convert(varchar, GetDate(), 120)

*/
SET NOCOUNT ON


DECLARE @Cmd varchar(4000),
              @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


IF @Debug = 1
       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


DECLARE @s varchar(Max)

-- Generate global table name
SET @GlobalTableName = '##FilesShow_'
                                         + Substring(system_user, CHARINDEX( '\', SYSTEM_USER) + 1, 100)
                                         + '_'
                                         + Convert(varchar, GetDate(), 120)


SET @GlobalTableName = REPLACE(@GlobalTableName, '.', '_')
SET @GlobalTableName = REPLACE(@GlobalTableName, ' ', '_')
SET @GlobalTableName = REPLACE(@GlobalTableName, ':', '')
SET @GlobalTableName = REPLACE(@GlobalTableName, '-', '')


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(35),
              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 ' + @GlobalTableName + '
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 TOP 1000 *
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 TOP 1000 *
FROM ' + @GlobalTableName + '
ORDER BY Date desc

'

IF @SuppressOutput = 0
       Print @QueryHelp2




No comments:

Post a Comment