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.

Tuesday, January 2, 2018

Script for relocating tempdb files

Quick script for relocating tempdb files.


-- Check location of all tempdb files before proceeding 
SELECT *
FROM sys.master_files
WHERE db_name(database_id) = 'tempdb'

-- tempdb (NOTE:  tempdb log goes on same disk as tempdb data for most cases)
ALTER DATABASE tempdb MODIFY FILE ( NAME = 'tempDev', SIZE = 2GB , FILEGROWTH = 1GB,
       FILENAME = 'T:\Sql\Data\tempdb.mdf')

ALTER DATABASE tempdb MODIFY FILE ( NAME = 'tempLog', SIZE = 500MB , FILEGROWTH = 500MB,
       FILENAME = 'T:\Sql\Data\tempdb.ldf')
     
-- You might have to do a service stop/restart before the next 3 lines will work
ALTER DATABASE tempdb REMOVE FILE temp2
ALTER DATABASE tempdb REMOVE FILE temp3
ALTER DATABASE tempdb REMOVE FILE temp4

ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev2', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'T:\Sql\Data\tempdb2.ndf' )
ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev3', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'T:\Sql\Data\tempdb3.ndf' )
ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev4', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'T:\Sql\Data\tempdb4.ndf' )
ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev5', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'T:\Sql\Data\tempdb5.ndf' )
ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev6', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'T:\Sql\Data\tempdb6.ndf' )
ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev7', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'T:\Sql\Data\tempdb7.ndf' )
ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev8', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'T:\Sql\Data\tempdb8.ndf' )


-- Check location of all tempdb files before proceeding – you may or may not have to do a service stop/start for the new properties to take effect.
SELECT *
FROM sys.master_files
WHERE db_name(database_id) = 'tempdb'

Monday, April 24, 2017

Renumber or rename files in a folder

SCENARIO
You take photos on your camera, they are named something similar to:
    DSC_0001.nef
    DSC_0002.nef
    ...
    DSC_0421.nef

During your photoshoot, your memory card becomes full so you swap cards.  After your photoshoot your new card has photos with these names:
    DSC_0001.nef
    DSC_0002.nef
    ...
    DSC_0141.nef

You want to rename the second batch of files to be renumbered, starting from 0422 ending at 0562.

I'm not sure what other photographers do, but since I also wear an SQL hat, I simply created this proc:


USE Admin
IF OBJECT_ID('dbo.cameraFileRenumber') Is Not Null
       DROP PROC dbo.cameraFileRenumber
go
CREATE PROC dbo.cameraFileRenumber
       @Path varchar(500),
       @Base varchar(30),
       @NumDigits int,
       @OffsetAdd int,
       @Debug bit = 1
AS
/*     DATE          AUTHOR        REMARKS
       1/1/17        PPaiva        Initial creation.

       DESCRIPTION
              Renumbers camera files with a numeric offset.

       SCENARIO
              You do a photoshoot with a memory card.  Photos are numbered as such:
                     DSC_0001.nef    to   DSC_0421.nef  (421 files)
              Your memory card fills up.  You change memory cards, the new photos are now numbered:
                     DSC_0001.nef    to   DSC_0141.nef  (141 files)
              You have a problem - you can't put all the files into the same folder because they
                     start counting from the same base number.  Therefore you have overlapping file names.
              Solution:  Rename the second set of files to start counting at 0422. 
              Desired end result:  DSC_0001.nef to     DSC_0562.nef  (562 files)


       USAGE
              -- Debug
              cameraFileRenumber
                     'K:\Images\Nikon\2017\2017-12-17_Elizabeth\batch2',
                     'DSC_', 4, 421
                     , 0

              -- Execute
              cameraFileRenumber 'K:\Images\Nikon\2017\2017-12-17_Elizabeth\batch2', 'DSC_', 422

       DEBUG
              xp_fixeddrives

              xp_cmdshell '

*/
SET NOCOUNT ON

DECLARE @s varchar(1000)

SET @s = 'dir /b "' + @Path + '"'

CREATE TABLE #t (
       ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
       Filename varchar(30),
       FilenameNoExt varchar(30),
       Extension varchar(10),
       sNumWithoutBase varchar(10),
       NumWithoutBase int,
       NewNumWithoutBase int,
       NewFilename varchar(30),
       RenameCommand varchar(1100)
       )

SET @Path = dbo.udfAddBackslashIfNec(@Path)


-- Get filenames into table
INSERT INTO #t (Filename)
       Exec xp_cmdshell @s

DELETE
FROM #t
WHERE Filename Is Null

IF @Debug = 1
       SELECT  Filename,
                     CHARINDEX('.', Filename) Pos,
                     Left(Filename, CHARINDEX('.', Filename) - 1) FilenameNoExt,
                     Substring(Filename, CHARINDEX('.', Filename) + 1, 100 ) ExtOnly
       FROM #t



UPDATE #t
SET FilenameNoExt = Left(Filename, CHARINDEX('.', Filename) - 1),
       Extension = Substring(Filename, CHARINDEX('.', Filename) + 1, 100 )

UPDATE #t
SET    sNumWithoutBase = Replace(FilenameNoExt, @Base, '')

UPDATE #t
SET NumWithoutBase = convert(int, sNumWithoutBase)
      
UPDATE #t
SET NewNumWithoutBase = NumWithoutBase + @OffsetAdd

UPDATE #t
SET NewFilename = @Base + dbo.udfPadLeft(NewNumWithoutBase, @NumDigits, '0') + '.' + Extension

UPDATE #t
SET RenameCommand = 'rename "' + @Path + Filename + '" "' + NewFilename + '"'

IF @Debug = 1
       SELECT  *
       FROM #t
       ORDER BY ID


DECLARE @MaxID int,
              @ID int,
              @Cmd varchar(1100)

SELECT @MaxID = Max(ID),
              @ID = 0
FROM #t


WHILE @ID < @MaxID
       BEGIN
              -- Get next ID
              SELECT @ID = Min(ID)
              FROM #t
              WHERE ID > @ID

              -- Get other attribs
              SELECT @Cmd = RenameCommand
              FROM #t
              WHERE ID = @ID

              IF @Debug = 0
                     Exec xp_cmdshell @cmd

              Print @cmd

       END