How to move tempdb database files in SQL Server

Detailed steps on how to move tempdb database files (mdf and ldf) from one location to another. Tempdb is recreated every time you restart SQL Server Instance. So all we have to do is update the new path in master database, and a restart of the sql server instance will create tempdb in the newly specified location. This change requires a restart of your SQL Server Instance, so make sure to do this during a maintenance window.

1. First find the tempdb database files location which we want to move. To do that, execute the following query.

use master
go
select db_name(database_id) 'DatabaseName', name 'LogicalFileName',physical_name 'PhysicalFileName'
from sys.master_files
where database_id = db_id('tempdb')
go

2. Now that you have the logical and physical file names, navigate to the new folder or drive where you have to move the files. That is., the destination folder or directory. Right click on the destination folder and give full access to the SQL Server service account.

3. Now update the new file locations in master database using the below query. This will update the new database files location for tempdb in the master database. Make sure to update the location for all tempdb files which you are moving.

use master
go
alter database tempdb
modify file (name = tempdev, filename = 'F:\MSSQL\NewLocation\tempdb.mdf');
go
alter database tempdb
modify file (name = templog, filename = 'L:\MSSQL\NewLocation\templog.ldf');
go

4. Restart your SQL Server Instance.

5. That’s all, tempdb is now created on a new path. Run the below query to verify the new path of the tempdb.

use master
go
select db_name(database_id) 'DatabaseName', name 'LogicalFileName',physical_name 'PhysicalFileName'
from sys.master_files
where database_id = db_id('tempdb')
go

6. Make sure to delete old tempdb files from the old location. They are no more required, and just using up the storage.

Click on the below link to learn more on moving other system databases and user databases in SQL Server.

Move User and System Databases in SQL Server