Moving the tempdb database(SQL server)

lkptantan發表於2008-04-08
DatabaseSQLServer

Moving the tempdb database

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:Sqldatatempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:Sqldatatemplog.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.
[@more@]

Moving the tempdb database

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:Sqldatatempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:Sqldatatemplog.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.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/167898/viewspace-1002068/，如需轉載，請註明出處，否則將追究法律責任。

相關文章