Move Tempdb

-----------------Move tempdb from C drive to D drive local disk drive-----------
1. Use below script to find file structure  

Use master 

GO 

  

SELECT  

name AS [LogicalName] 

,physical_name AS [Location] 

,state_desc AS [Status] 

FROM sys.master_files 

WHERE database_id = DB_ID(N'tempdb'); 

GO 

2.  Use below scripts to change tempdb location, but don’t forget to restart  SQL Server Service to after this changes 

----- 

-- Need to restart server/ SQL Server Service after this change 

USE master; 

GO 

  

ALTER DATABASE tempdb  

MODIFY FILE (NAME = tempdev, FILENAME = 'D:\tempdb.mdf'); 

GO 

  

ALTER DATABASE tempdb  

MODIFY FILE (NAME = tempdev2, FILENAME = 'D:\tempdev2.ndf'); 

GO 

  

ALTER DATABASE tempdb  

MODIFY FILE (NAME = templog, FILENAME = 'D:\templog.ldf'); 

GO