Step 1: first we have to check the location of TempDB:-
USE tempdb GO SELECT * FROM sys.database_files
Step 2: Get Logical File Name
USE TempDB GO EXEC sp_helpfile GO
If you have not done any customization most of the time then you will see the logical file name as ‘tempdev‘ for mdf data file and ‘templog‘ for log files.
Step 3: Now we have to execute the ALTER DATABASE statement with MODIFY FILE option:
USE master; GO ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'D:SystemDatabases\tempDB\tempdev.mdf' ); GO
ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = 'D:\SystemDatabases\tempDB\templog.ldf' ); GO
When we will execute above script then we see the message that query has run successfully, but The location of the TempDB changed when SQL Server will restart services again and we will be able to see the changes only after the services are restarted.