<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Performance Tuning
»
Shifting TEMPDB database to another folder in...
Shifting TEMPDB database to another folder in same server.
Rate Topic
Display Mode
Topic Options
Author
Message
Joy Smith San
Joy Smith San
Posted Sunday, December 21, 2008 4:14 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 1,370,
Visits: 2,289
Dear All,
I want to change the location of my tempDB data base.
How can I dot it..? I also want to do it without stopping SQL Server..
Is it possible..? Please guide.
Thanks in advance.
Santhosh.
Post #623559
Adi Cohn-120898
Adi Cohn-120898
Posted Sunday, December 21, 2008 4:50 AM
SSCrazy
Group: General Forum Members
Last Login: Today @ 2:19 AM
Points: 2,018,
Visits: 4,915
You can do it by running alter database statement. You can look at the script .that I’ve added as an example. Unfortunately you'll have to restart the service. Without restarting the service, tempdb will continue to work from its current position.
use master
go
--Change the location of the data file. .
Alter database tempdb modify file (name = tempdev, filename = 'D:\MyTempDBDir\tempdb.mdf')
Go
--Change the location of the log file.
Alter database tempdb modify file (name = templog, filename = 'E:\TempDBLogDir\templog.ldf')
go
Don't forget to use the real path and not the path in this example.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #623561
Joy Smith San
Joy Smith San
Posted Sunday, December 21, 2008 4:57 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 1,370,
Visits: 2,289
Thanks...will try the same.
Post #623562
ps.
ps.
Posted Sunday, December 21, 2008 5:04 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236,
Visits: 3,620
Use ALter database with Modify file option to move the tempdb database and do an instance restart.
FOllowing links would be helpful.
http://www.sqlservercentral.com/articles/Administration/63568/
http://support.microsoft.com/kb/187824
or in BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/72bb62ee-9602-4f71-be51-c466c1670878.htm
Pradeep Singh
Post #623565
TheSQLGuru
TheSQLGuru
Posted Sunday, December 21, 2008 5:07 PM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 12:32 AM
Points: 3,574,
Visits: 5,112
I post this for the benefit of others who may search the forums: PLEASE check Books Online before posting questions! It is a WONDERFUL resource! I put 'tempdb' in the index and voila, there was a clickable link 'moving', which took me directly to everything you need to know about moving the tempdb. :)
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #623667
Jeff Moden
Jeff Moden
Posted Sunday, December 21, 2008 5:15 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902,
Visits: 26,783
Nah... too much work, Kevin. ;)
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #623668
GilaMonster
GilaMonster
Posted Monday, December 22, 2008 12:43 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 3:05 AM
Points: 37,675,
Visits: 29,927
TheSQLGuru (12/21/2008)
PLEASE check Books Online before posting questions!
Read the manual? What a radical, way out idea!
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #623749
TheSQLGuru
TheSQLGuru
Posted Monday, December 22, 2008 8:37 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 12:32 AM
Points: 3,574,
Visits: 5,112
Well, some manuals (especially these days) aren't very good. But the 'softies really did a nice job with SQL Server's BOL! Most people don't know that. I have it open 24/7 on my laptop.
I sure do miss the old days though, where you could get nice hard-copy manuals though. Gosh - that sounds SOOOO old school!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #623998
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
