Move Tempdb - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Move Tempdb

Author

Topic

johnmcc
Starting Member

4 Posts

Posted - 2007-08-13 : 13:38:33

Hi, we are trying to move the tempdb to another drive but the option is not available, do we need to reinstall SQL to accomplish this?

thanks in advance,

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-13 : 13:46:06

TO MOVE TEMPDB

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.

Use the ALTER DATABASE statement, specifying the logical file name as follows:
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'D:\Sql\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'D:\Sql\templog.ldf')
where D:\Sql\ is the location where you want to move. It can be anywhere u want.

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.

Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
Stop and then restart SQL Server.

Ashley Rhodes

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-08-13 : 14:25:50

every DBA should have a bookmark to this KB article http://support.microsoft.com/kb/224071

-ec

johnmcc
Starting Member

4 Posts

Posted - 2007-08-13 : 15:26:17

Awesome, we'll give that a shot

thanks,

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-13 : 17:42:46

Let me know how you get on
http://www.databasejournal.com/features/mssql/article.php/3379901

Im about to do the same thing but in the thread it mentions some changes in the startup i thinking that is just for master and all i have to do is what it says for the alter part.

What happens if anyone is on the machine...i take it all users have to be off...how to prevent any one being on there.
Can you do anytime.

Does it require a shutdown.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-13 : 17:45:05

What is the recommedation for RAID level for the TEMPDB
Is this RAID 5, 1, 10.

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-13 : 22:51:22

You have to restart sql to move tempdb, and use raid 10 whenever possible.

marbos
Starting Member

7 Posts

Posted - 2007-08-16 : 16:26:21

I would disagree with the RAID level on TempDB as the database is recreate each time SQL Server restarts and the added redundancy on the LUN is probably not necessary unless you have the additional drives to throw at it.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-08-16 : 16:47:47

quote:
Originally posted by marbos

I would disagree with the RAID level on TempDB as the database is recreate each time SQL Server restarts and the added redundancy on the LUN is probably not necessary unless you have the additional drives to throw at it.

RAID 10 is used primarily for performance, not just redundancy. That is why RAID 5 was not suggested.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources