August 20, 2003 at 1:17 pm
Can any one tell me what is the best way to move Transaction Logs and TempDB.
Thanks
venkatesh
venkatesh
August 20, 2003 at 2:24 pm
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 confirming 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.
3.Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
4.Stop and restart SQL Server.
August 21, 2003 at 4:54 am
And for the transaction logs (which is at least one er database) the best was is detach the DB move your files whereever you want and after attach it again.
It works well a fast
Bye
Gabor
Bye
Gabor
August 21, 2003 at 5:11 am
Just by curiosity, In what cases would I need to move tempdb files. Isn't the TEMDB recreated once sql server is restarted.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
August 21, 2003 at 7:35 am
By default, TempDB is created in same folder as other system databases such as master. The drive that holds these databases may not large enough for TempDB.
August 21, 2003 at 7:50 am
The only doubt is if i detach DB files and Attach datafiles , is there will be any issues with logins connecting to that databases.
venkatesh
venkatesh
August 21, 2003 at 8:17 am
Logins are in master database. If you detach and attach database from one server to another server, you may have to move the logins too.
Edited by - allen_cui on 08/21/2003 08:21:58 AM
August 21, 2003 at 10:31 am
Sure TempDB is recreated everytime SQL Server is re-started but, it will recreate wherver the location of the files have been specified. Lot of times it is a good idea to move the TempDB to it's own drives isolating from user database files for better performance and more space.
-Sravan
quote:
Just by curiosity, In what cases would I need to move tempdb files. Isn't the TEMDB recreated once sql server is restarted.Pay Respect to People on your way up. For you will meet the same People on your way down.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply