Jul 20, 2017

How to move TempDB from one drive to another drive (New Drive)

Get Logical File Name and location of TempDB

USE TempDB
GO
EXEC sp_helpfile
GO

Or

SELECT name ,physical_name AS Location
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')
GO

Change the location of TempDB

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:\datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:\datatemplog.ldf')
GO



Need to restart the Service of SQL Server to effect the changers