Tuesday, October 07, 2008

Move TEMPDB to a Separate Drive

It can be beneficial to move TEMPDB to an alternate location for improved performance. I've run across installations where tempdb was located on the OS drive with autogrow enabled and the drive nearly ran out of space!

This change requires a restart of the SQL Server Service(MSSQLSERVER).

Here's an example in SQL Server 2005 of moving TEMPDB to the (T:) drive with one data file per CPU(four CPUs). On the box used in this example, the (T:) drive is on a SAN.

SELECT name, physical_name
FROM
sys.master_files
WHERE
database_id = DB_ID('tempdb');
GO

ALTER
DATABASE tempdb
MODIFY
FILE (NAME = tempdev, FILENAME = 'T:\SQLTEMPDB\tempdb.mdf');
GO
ALTER
DATABASE tempdb
MODIFY
FILE (NAME = templog, FILENAME = 'T:\SQLTEMPDB\templog.ldf');
GO

ALTER
DATABASE [tempdb] ADD FILE ( NAME = N'tempdb1', FILENAME = N'T:\SQLTEMPDB\tempdb1.ndf' , SIZE = 100MB , FILEGROWTH = 50MB )
GO
ALTER
DATABASE [tempdb] ADD FILE ( NAME = N'tempdb2', FILENAME = N'T:\SQLTEMPDB\tempdb2.ndf' , SIZE = 100MB , FILEGROWTH = 50MB )
GO
ALTER
DATABASE [tempdb] ADD FILE ( NAME = N'tempdb3', FILENAME = N'T:\SQLTEMPDB\tempdb3.ndf' , SIZE = 100MB , FILEGROWTH = 50MB )
GO
ALTER
DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 100MB , FILEGROWTH = 50MB )
GO
ALTER
DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 10MB , FILEGROWTH = 10MB )
GO

SELECT
name, physical_name
FROM
sys.master_files
WHERE
database_id = DB_ID('tempdb');
GO

--restart needed to affect changes

No comments: