Menu & Search

Moving TempDB Files in SQL Server

Moving TempDB Files in SQL Server

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.

SQL Server TempDB Files

5. Run sp_helpfile again.

sp_helpfile SQL Server

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

Shrink TempDB Files in SQL Server

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

Send Email in SQL Server

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

TempDB Files in SQL Server

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