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