How to Move tempDB to another drive in Sql Server ?

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.

 

 

---