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.

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



Thursday, December 1, 2011

BackupDB

Handy procedure for making database backups a snap.

All you need to do is specify the database name and the path, and optionally add the date or date/time to the backup filename. 

You can also pass in ALL for @DB to backup all databases on this SQL instance.  Very handy when you need to backup a whole server and then restore the files onto another server, as in creating a development server based on production.  

Making large quantities of backups is much more efficient when done via script, as contrasted with right-clicking each database in the object explorer.

CAVEAT:  Always check to see if a given database is having its transaction logs backed up.  If it is, keep in mind that making a full backup will render the previous transaction log backups useless! 

This proc has a couple of dependencies which are easy to install:
        udfAddBackslashIfNec
        DoesPathExist
       

USE Admin
GO

IF object_id('dbo.BackupDB') Is Not Null
      DROP PROC BackupDB
GO

CREATE PROC dbo.BackupDB
      @DB sysname,
      @Path varchar(Max),
      @DateTimeFormat varchar(2) = Null,
      @Mode smallint = 0
AS
/*    DATE              AUTHOR            REMARKS
      12/1/11                 PPaiva            Initial creation.

      DESCRIPTION
            Makes a backup of the database and saves to @Path.  Backup
                  file is always initialized (WITH INIT) so it always
                  contains no more than one backup set.
            Optionally set @Mode = 1 and simply copy/paste the script from the output pane.

            @DB can be a specific database, or 'ALL' to backkup all DBs.
           
            @Mode - 0 - Script only
                        1 - Execute only (make backup)
                        2 - Script and Execute
                       
            @DateTimeFormat
                        D  - Date
                        DT - DateTime
              all else - No date or time - just database name

      DEBUG
            master..xp_fixeddrives
            master..xp_cmdshell 'dir /ogn C:\Paul\SqlData\Bak'

      DEPENDENCIES
            udfAddBackslashIfNec()
            DoesPathExist
           
      USAGE
            -- Get syntax help by passing in an illegal value for @DateTimeFormat
            Exec BackupDB 'Admin', 'C:\Paul\SqlData\Bak', '?'

            -- Script for backing up ALL databases
            Exec BackupDB 'All', 'C:\Paul\SqlData\Bak', Null
           
            -- Backup a specific database
            Exec BackupDB 'Admin', 'C:\Paul\SqlData\Bak'
            Exec BackupDB 'Admin', 'C:\Paul\SqlData\Bak', 'D'
            Exec BackupDB 'Admin', 'C:\Paul\SqlData\Bak', 'DT'

*/

SET NOCOUNT ON

DECLARE @FilePathName varchar(Max),
            @sDate varchar(50),
            @Sql varchar(Max)

-- Help info
IF IsNull(@DateTimeFormat, '') Not In ('', 'D', 'DT')
      BEGIN
            SET @Sql = 'Only the following values are allowed for @DateTimeFormat:

@DateTimeFormat     EXAMPLE BACKUP FILENAME             DATE/TIME FORMAT
---------------     ---------------------------------   -------------------
    Null              Northwind.bak
     D                Northwind_2011-12-01.bak            YYYY-MM-DD
     DT               Northwind_2011-12-01_150253.bak     YYYY-MM-DD_HHMMSS'

            RaisError (@Sql, 0, 1)
            RETURN

      END

IF @Mode Not In (0, 1, 2)
      BEGIN
            RaisError ('@Mode must be 0, 1, or 2.', 16, 1)
            RETURN
      END

-- Check for valid @Path
DECLARE @Out bit
Exec DoesPathExist @Path, @Out OUTPUT

IF @Out = 0
      BEGIN
            SET @Sql = 'The supplied path <' + IsNull(@Path, '(Null)') + '> is not valid.'
            RaisError (@Sql, 16, 1)
            RETURN
      END


SET @DB = IsNull(@DB, '')


-- Check for valid DB
IF @DB <> ''  AND @DB <> 'All'
      IF NOT EXISTS(SELECT * FROM master.dbo.sysdatabases WHERE Name = @DB )
            BEGIN
                  SET @Sql = 'Database <' + @DB + '> was not found on server ' + Convert(varchar, ServerProperty('ServerName')) + '.'
                  RaisError (@Sql, 16, 1)
                  RETURN
            END




SET @Path = Admin.dbo.udfAddBackslashIfNec(@Path)

IF @DateTimeFormat = 'D'
      -- YYYY-MM-DD
      SET @sDate = '_' + Convert(varchar(10), GetDate(), 120)

ELSE IF @DateTimeFormat = 'DT'
      -- YYYY-MM-DD_HHMMSS.bak'
      SET @sDate = '_' + Convert(varchar(10), GetDate(), 120) + '_' +
                  Substring(Convert(varchar(20), GetDate(), 120), 12, 2) +
                  Substring(Convert(varchar(20), GetDate(), 120), 15, 2) +
                  Substring(Convert(varchar(20), GetDate(), 120), 18, 2)
ELSE
      SET @sDate = ''


-- Determine which DBs
CREATE TABLE #BackupDB (
      Name varchar(100) NOT NULL PRIMARY KEY
      )

IF @DB = 'ALL'
      INSERT INTO #BackupDB
            SELECT Name
            FROM master.dbo.sysdatabases
            WHERE Name Not In ('tempdb')  -- not possible to backup tempdb
ELSE
      INSERT INTO #BackupDB
            SELECT Name
            FROM master.dbo.sysdatabases
            WHERE Name = @DB



DECLARE @MaxDB varchar(100),
            @ThisDB varchar(100)

SELECT  @MaxDB = Max(Name),
            @ThisDB = ''
FROM #BackupDB

WHILE @ThisDB < @MaxDB
      BEGIN
            -- Get next DB
            SELECT @ThisDB = Min(Name)
            FROM #BackupDB
            WHERE Name > @ThisDB

            SET @FilePathName = @Path + @ThisDB + @sDate + '.bak'                        
            SET @Sql = 'BACKUP DATABASE [' + @ThisDB + ']
TO DISK = ''' + @FilePathName + ''' WITH INIT'

     
            -- Script only
            IF @Mode = 0
                  BEGIN
                        Print @Sql
                        Print ''
                  END

            -- Execute only
            IF @Mode = 1           
                  BEGIN
                        Print 'Backup file:  ' + @FilePathName
                        Print ''
                        Exec (@Sql)
                  END

            -- Script and Execute
            IF @Mode = 2
                  BEGIN
                        Print @Sql
                        Print ''
                        Exec (@Sql)
                  END
           


      END



Tuesday, November 22, 2011

udfAddBackslashIfNec

Handy function for ensuring a given path ends in a backslash. 

After being burned enough times calling a path + filename without the necessary backslash at the end of the path, I decided to fortify all my code with this function. 

It simply checks for a backslash at the end.  If not present, it adds one.

USE Admin
GO

CREATE FUNCTION dbo.udfAddBackslashIfNec(
      @In varchar(1000)
      )
RETURNS varchar(1001)
AS
/*    DATE        AUTHOR            REMARKS
      11/22/11    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