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'