Moving the tempdb database
You can move tempdb files by using the ALTER DATABASE statement.
- Determine the logical file names for the tempdb database by using sp_helpfile as follows:
use tempdb
go
sp_helpfile
go
The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.
- Use the ALTER DATABASE statement, specifying the logical file name as follows:
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
You should receive the following messages that confirm the change:
Message 1
File ‘tempdev’ modified in sysaltfiles. Delete old file after restarting SQL Server.
Message 2
File ‘templog’ modified in sysaltfiles. Delete old file after restarting SQL Server. - Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
- Stop and then restart SQL Server.