It’s a solid best practise to have TempDB files stored in separate drive, away from your C: drive.

This is because TempDB can grow very large, very quickly (within minutes); depending on workloads of course, and this can happen on both the TempDB data (.ndf) and log (.ldf) files.

This post is a quick tutorial on how to move all TempDB files from one location to another in SQL Server. A planned maintenance window is required on production servers for this one.

Moving TempDB Files

1. Use sp_helpfile to view logical TempDB names.

2. Before running the SQL below, amend the name and full file path within the single quotes.

(You’ll need an ALTER statement per file – if copy/paste isn’t fast enough, here’s a post by Brent Ozar to script out the statement.)

3. As mentioned in the output message, the path will be used the next time the database is started. So we need to restart the SQL Server service!

4. Once that’s done, I like to visually check the new files.

5. Run sp_helpfile again.

6. Have a look at the location of files within the Shrink File window.

7. Send a test Database Mail (people do this). 

8. And finally, remember to remove the old TempDB files, especially if you’re moving to another folder on the same disk!

(You’re unable to copy or delete TempDB files if their being used by an online SQL Server service.)