How to move TEMPDB to another drive

TEMPDB is a build-in temporary database. It is used for:

  • Bulk operations
  • Common table expression queries
  • Cursors
  • Event notification
  • Indexes/sort_in_tempdb
  • Very large object (varchar,text, image...)
  • Row versioning
  • Triggers
  • XML

Microsoft recommends that you move the TEMPDB database, to it's own drive.

TEMPDB files location

The TEMPDB database consists of 2 files:

  • tempdev
  • templog

It is very highly recommended that you move both, the datafile and the log file. 

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

File Name       File Directory
--------------- -----------------------------------------------------------------------
tempdev         C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf
templog         C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf

(2 row(s) affected)

Move TEMPDB

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'c:\syv\tempdb.mdf');
GO
ALTER DATABASE  tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'c:\syv\templog.ldf');
GO

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

Please note:

  1. The new path will be used the next time the database is started.
  2. The database used is: MASTER.
  3. You must restart SQL Server.

Verification of TEMPDB move

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

File Name       File Directory
--------------- -------------------
tempdev         c:\syv\tempdb.mdf
templog         c:\syv\templog.ldf

(2 row(s) affected)