Best approach for moving tempdb .ndf files

Question

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

2. Stop SQL Server (the instance isn't doing anything currently).
3. copy/paste the 3 .ndf files from their current C: location to the new F:\MSSQLData\ location
4. Restart SQL Server.
5. 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).

Answer 1

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.

Remember to manually delete the files from the old location after step 5.

Answer 2

As mentioned, if the files doesn't exist, then they are created at startup. So the question is whether to delete them from the old location and lest SQL Server creates new ones? Or to move the files so SQL Server don't have to create new ones? One could argue that one of these would be quicker than the other. If that isn't an issue, then use whichever feels best for you.

One factor is whether you have Instant File Initialization turned on. Without it, you'll se a longer time to create data files.

My guess is that for that file size, you won't see any significant difference. I.e., use whichever method.