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');