Sql-server – Best approach for moving tempdb .ndf files

alter-databaseconfigurationsql servertempdb

During a SQL Server installation, I "thought" I specified location of tempdb on 'F:' and not 'C:', but I can see that although tempdb.mdf and templog.ldf are on 'F:', the 3 .ndf secondary data files are on 'C:'.

I'm thinking of moving the .ndf files by performing following steps:

    1. 
    ALTER DATABASE tempdb MODIFY FILE ( NAME = temp2 , FILENAME = 'F:\MSSQLData\temp2' )
    ALTER DATABASE tempdb MODIFY FILE ( NAME = temp3 , FILENAME = 'F:\MSSQLData\temp3' )
    ALTER DATABASE tempdb MODIFY FILE ( NAME = temp4 , FILENAME = 'F:\MSSQLData\temp4' )
  1. Stop SQL Server (the instance isn't doing anything currently).
  2. copy/paste the 3 .ndf files from their current C: location to the new F:\MSSQLData\ location
  3. Restart SQL Server.
  4. Check if it worked:
    SELECT name, physical_name AS CurrentLocation, state_desc  
    FROM sys.master_files  
    WHERE database_id = DB_ID(N'tempdb');

Is that the best approach? The instance supports only 3 small databases (<10GB total).

Best Answer

The best approach is the one you can find on the Moving the tempdb database section of the documentation.

Your method is close to the one Microsoft recommends, except by the 2nd and 3rd steps you listed and the reason is:

Because tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files.

Related Question