Best approach for moving tempdb .ndf files

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


• Stop SQL Server (the instance isn't doing anything currently).

• copy/paste the 3 .ndf files from their current C: location to the new F:\MSSQLData\ location

• Restart SQL Server.

• 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).

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.

StackExchange Database Administrators Q#251615, answer score: 3