June 18, 2008 at 10:28 am
Hi,
I need to relocate tempdb to another drive. Obviously. sp_detach_db does not work. Is there a way to do it without stopping the server ?
In case if I will have to stop the server anyway, how it wil recognize new tempdb location at the startup ?
Thanks
June 18, 2008 at 10:46 am
Try This:
USE MASTER
Alter database tempdb modify file (name = tempdev, filename = 'E:\NEWFOLDER\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'F:\LOGFOLDER\templog.ldf')
Maninder
www.dbanation.com
June 18, 2008 at 11:31 am
I am not aware of any way to do it other than restarting the SQL Service. BOL has a topic which specifically addresses moving system databases and includes an example for how to move tempdb.
Link: http://msdn.microsoft.com/en-us/library/ms345408.aspx
One note - if you plan on resizing tempdb do it after you move to the new drive; if you do it before the move (or as part of the same command) SQL will try to do the resize operation. If you don't have enough disk space on the disk where it currently sits you'll get an error.
June 18, 2008 at 11:51 am
Use the command Mani given and once done restart sql services.. then delete the old files from the old directory...
June 18, 2008 at 1:29 pm
my favorite method is the following
create new tempdb files on the drives you want them on
restart SQL
shrink the default tempdb files if they are large
set growth restrictions on them of a few MB
June 18, 2008 at 2:28 pm
The link that Kendal referred to has a full example of exactly how to do this along with certain explanations as to why you need to do (or not do) certain things. Even though most of the post on this thread are correct, it's worth reading the link... it's worth learning the "why" along with the "how". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2008 at 2:58 pm
Hi,
Why move tempdb?
http://www.sqlteam.com/article/moving-the-tempdb-database
Working with tempdb in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
Regards
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply