Moving tempdb's database files

By default, tempdb is placed on the same drive that SQL Server is installed on. You might not want this, often because the engine will be installed on a relatively small applications partition rather than on a bigger (and perhaps differently RAIDed etc) data partition. This article, based on a tech-recipe, describes how to move tempdb's files.

1. From an SSMS query window (connected to the required server), run

   ALTER DATABASE tempdb
   MODIFY FILE (NAME = tempdev, FILENAME = '<NewLocation>\tempdb.mdf');
   GO
   ALTER DATABASE tempdb
   MODIFY FILE (NAME = templog, FILENAME = '<NewLocation>\templog.ldf');
   GO

2. Restart SQL Server

3. Double-check that tempdb has moved as required:

   SELECT 
     name
   , physical_name  -- will show files in new location
   FROM sys.master_files
   WHERE database_id = DB_ID('tempdb');