How to Move TempDB

In order to move TempDB, use the alter database command with “modify file” and specify a new path.

[cc lang=”sql”]
use master
go
Alter database tempdb modify file (name = tempdev, filename = ‘e:tempdbtempdb.mdf’)
go
Alter database tempdb modify file (name = templog, filename = ‘e:tempdbtemplog.ldf’)
go
[/cc]

Make sure the folder path already exist otherwise you will get an error.

Once complete, an informative message will appear stating that tempdb will be moved the next time SQL is started.

[code]

The file “tempdev” 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.

[/code]

This will require a restart of the SQL Server Database Engine service.