Moving the tempdb database

There are cases when you might want to move tempdb database from an existing drive to a new drive.

1. When the drive is full and you are in a situation where you cannot extend that drive.
2. Move tempdb to a separate drive to increase its performance.

This is a simple process and cannot be done by detaching and attaching the database, as we cannot attach or detach a system database.
Also we need to restart the SQL services.

Here is the process how we can move the tempdb to a new location.

1. First get the list of tempdb files by using this query
select name,physical_name from sys.master_files where DB_NAME(database_id)='tempdb'
2. Then for each tempdb file that you need to move, execute statements like below

Alter Database tempdb modify
file (NAME = 'tempdev' , 
FILENAME = 'Drive:\Path\tempdb.mdf') -- Mention the new location

Alter Database tempdb modify
file (NAME = 'templog' , 
FILENAME = 'Drive:\Path\templog.ldf') -- Mention the new location

3. Stop SQL Services
4. Start SQL Services
5. Verify the new Location
   select name,physical_name from sys.master_files where DB_NAME(database_id)='tempdb'