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:
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:
Alter database tempdb modify file (name = tempdev, filename = ‘E:\Sqldata\tempdb.mdf’)
Alter database tempdb modify file (name = templog, filename = ‘E:\Sqldata\templog.ldf’)
You should receive the following messages that confirm the change:
File ‘tempdev’ modified in sysaltfiles. Delete old file after restarting SQL Server.
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.