There is a requirement to move a TempDB file from one drive to another. There may be many different reasons – such as the Tempb is FULL or it is a scheduled change.   Moving a TempDB file requires executing the ALTER DATABASE command and a SQL Server Restart.

View the current location of the TempDB files by :

use tempdb
go
select fileid,name,[filename] from sys.sysfiles

--the output is something like
--fileid	name	filename
--1	tempdev	G:\MSSQLSERVER\MSSQL$INST1\Data\tempdb.mdf
--2	templog	F:\MSSQLSERVER\MSSQL$INST1\Data\templog.ldf
--3	tempdev1	E:\MSSQLSERVER\MSSQL$INST1\Data\tempdev1.ndf

You’ll notice in the output three files, the “tempdev1” file was added as an extra file earlier on. All I want to complete is moving the”tempdev1” file, but the same principle applies to the tempdev and templog file

USE master GO ALTER DATABASE TempDB 
MODIFY FILE (NAME = tempdev1, FILENAME = 'J:\tempdev1.mdf') 
--then a SQL Server restart

 Read More

TempDB performance and strategy checklist - SQL Server DBA

SQL Server – List current tempdb statements - SQL Server DBA

Shrink tempdb without sql server restart - SQL Server DBA

SQL Server – tempdb and solid state drives - SQL Server DBA