Saturday, November 10, 2007

Moving TempDB / Splitting TempDB to multiple files

Script as below.
You need to restart SQL for tempdb to be recreated in the new locations...

USE master
GO

-- move tempdb data
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev,
FILEGROWTH = 10% ,
MAXSIZE = UNLIMITED,
SIZE=1000MB ,
FILENAME = 'D:\databases\tempdb_1.mdf')
GO

-- move tempdb log
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, SIZE=1, FILENAME = 'D:\databases\templog.ldf')
GO

-- additional processor? split tempdb into equal filesizes, 1 per processor...

ALTER DATABASE tempdb ADD FILE
(NAME = tempdev_2,
FILEGROWTH = 10% ,
MAXSIZE = UNLIMITED,
SIZE=1000MB ,
FILENAME = 'D:\databases\tempdb_2.mdf')
GO

No comments: