Moving the tempdb database
You can move tempdb files by using the ALTER DATABASE statement.
|
1. |
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.
|
|
2. |
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.
|
|
3. |
Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
|
|
4. |
Stop and then restart SQL Server. |