Safe way to move the TempDB

[jeffmoore64]

What is the SOP for moving my tempdb. I want to place it on a different drive.
Can I do this with out restarting SQL Server?
Version is sqlsrv 2000

TIA
Jeff

 

[ptheriault]

If you want to move the tempdb you have to run the ALTER database command.

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'D:\programs\mssql\data\tempdb.mdf')--your path
go
Alter database tempdb modify file (name = templog, filename = 'D:\programs\mssql\data\templog.ldf')--your path
Go

However, you have no choice but to restart the instance for this to take effect.



- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Take a look at this link too. There is some good information on how to properly manage your tempdb.

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[aalmeida]

You will have to restart SQL server services to move the tempdb, and you can use this:

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

from this article:

http://support.microsoft.com/kb/224071

I hope this helps

AL Almeida
Senior DBA

http://www.ajmedia.net/

"May all those that come behind us, find us faithfull"