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'

No comments:

Post a Comment