Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Shifting TEMPDB database to another folder in same server. Expand / Collapse
Author
Message
Posted Sunday, December 21, 2008 4:14 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:25 AM
Points: 1,519, Visits: 2,550
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
Posted Sunday, December 21, 2008 4:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 2,112, Visits: 5,427
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
Posted Sunday, December 21, 2008 4:57 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:25 AM
Points: 1,519, Visits: 2,550
Thanks...will try the same.
Post #623562
Posted Sunday, December 21, 2008 5:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 1, 2014 1:26 PM
Points: 2,242, Visits: 3,645
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
Posted Sunday, December 21, 2008 5:07 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:50 PM
Points: 4,351, Visits: 6,167
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
Posted Sunday, December 21, 2008 5:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
Nah... too much work, Kevin. ;)

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #623668
Posted Monday, December 22, 2008 12:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
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
Posted Monday, December 22, 2008 8:37 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:50 PM
Points: 4,351, Visits: 6,167
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 »

Add to briefcase

Permissions Expand / Collapse