Moving TempDB To Another Drive

Because tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files.

Remember to restart SQL Server after you run the move script for the changes to go into effect.

1. Determine the logical file names of the tempdb database and their current location on the disk. There may be multiple tempdb files.

SELECT 
	  name
	, physical_name AS CurrentLocation  
	, state_desc
FROM 
	sys.master_files  
WHERE 
	database_id = DB_ID(N'tempdb');  

2. Change the location of each file by using ALTER DATABASE

USE master;  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');  
GO  
ALTER DATABASE tempdb   
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');  
GO  

3. Stop and restart the instance of SQL Server.

4. Verify the file change.

5. Delete the tempdb.mdf and templog.ldf files from the original location.

The file "temp" has been modified in the system catalog. The new path will be used the next time the database is started.
 The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

Note: Because tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files. The files are created in the new location when the service is restarted in step 3. Until the service is restarted, tempdb continues to use the data and log files in existing location.

Multiple Files:

SELECT 
	  name
	, physical_name AS CurrentLocation  
	, state_desc
FROM 
	sys.master_files  
WHERE 
	database_id = DB_ID(N'tempdb');  

USE master;  
GO  

ALTER DATABASE tempdb   
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\tempdb.mdf');  
GO  

ALTER DATABASE tempdb   
MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\templog.ldf');  
GO  

ALTER DATABASE tempdb   
MODIFY FILE (NAME = temp2, FILENAME = 'T:\MSSQL\tempdb_mssql_2.ndf');  
GO  

ALTER DATABASE tempdb   
MODIFY FILE (NAME = temp3, FILENAME = 'T:\MSSQL\tempdb_mssql_3.ndf');  
GO  

ALTER DATABASE tempdb   
MODIFY FILE (NAME = temp4, FILENAME = 'T:\MSSQL\tempdb_mssql_4.ndf');  
GO  

Note:

• Make sure NT SERVICE\MSSQLSERVER account or the specific service account have write access to the new temp db folder.
• SQL Server will not start if there is something wrong with the temp db. Check SQL Server logs for more information.

C:\Program Files\Microsoft SQL Server\MSSQLx.MSSQLSERVER\MSSQL\Log

SQL Server Not Start due to Temp DB

Start you instance in master-only recovery mode…

NET START MSSQLSERVER /f /T3608

Then use the correct statement to relocate youre tempdb files.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/de424801-1d64-40ae-9b71-a6f5ed6189c9/made-a-mistake-moving-tempdb-sql-server-wont-start-now?forum=sqltools

http://msdn.microsoft.com/en-us/library/ms345408.aspx

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-2017

How to Move TempDB to New Drive in SQL Server

https://learn.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-ver16