How to move TEMPDB files to a new drive?

Moving the tempdb database in SQL Server is a simple process but does require the service to be restarted.

By default, the tempdb database is created in the same location as master, model and msdb.  tempdb is used to store temporary user created objects (e.g. temp tables), temporary internal objects (e.g. work tables for sorting) and any row version data.  Each time SQL Server is started all the objects in tempdb are deleted but the file sizes are retained.  People primarily want to move tempdb to increase performance or deal with file size issues.

Moving a tempdb files is a often task for a DBA supporting a big environment. Moving a tempdb is 4 step easy process.

1. Current tempdb location, make a note of current file location using the following code.

name      fileid filename
--------- ------ -------------------------------
tempdev        1 c:\Data\MSSQL\data\tempdb.mdf
templog        2 c:\Data\MSSQL\data\templog.ldf

2. Check and confirm that new location, where you want to place a tempdb files should be accessible by the SQL Server, this is very important step, specially for cluster boxes, this step ensures that you have added a new disk to sql server dependency list.  Here I am assuming, H:\MSSQL\Data\ is a new location, where you want to place tempdb files.

3. Use ALTER DATABASE tempdb, TSQL statement to specify new location, where you want to place your tempdb files.

4. Restart SQL Services, the new location, will be used, only after the restart

5. Delete the old files from the old directory, you made a note in Step 1

If you wanted to added multiple tempdb files to SQL Server that too in a new location, please view the following video, which will guide you, how you move tempdb to a new location and how you add multiple files to a tempdb.

Video, which covers,  1. How To Move TEMPDB Files to a new drive  2. How to add multiple files to tempdb