G. Moving tempdb to a new location

The following example moves tempdb from its current location on the disk to another disk location. Because tempdb is re-created each time the MSSQLSERVER service is started, you do not have to physically move the data and log files. The files are created when the service is restarted in step 3. Until the service is restarted, tempdb continues to function in its existing location.

Determine the logical file names of the tempdb database and their current location on disk.

SELECT name, physical_name

FROM sys.master_files

WHERE database_id = DB_ID('tempdb');

GO

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 = 'E:\SQLData\templog.ldf');

GO

Stop and restart the instance of SQL Server.

Verify the file change.

SELECT name, physical_name

FROM sys.master_files

WHERE database_id = DB_ID('tempdb');

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