Moving tempdb to other drives

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

Hi,

Is there any way to move the tempdb database to other drive? Currently the tempdb is in C drive. But C drive has only 10GB of diskspace.

There are a few times when I try to run a job, the tempdb grows very fast, an error message popup saying the tempdb is full. I think this is probably caused by the limited diskspace on C drive.

Can I move the tempdb to other drive? Like what we did to move other databases by detaching the database and move to other drives and then attaching it back.

Thanks.

-- Anonymous, August 11, 2004

Answers

Teoh,

Use the ALTER DATABASE statement to move tempdb's two files. For example: use master go Alter database tempdb modify file (name = tempdev, filename = 'D:\Sqldata\tempdb.mdf') go Alter database tempdb modify file (name = templog, filename = 'D:\Sqldata\templog.ldf') go

Then, stop and restart sql server, and you should be fine. In this document you will find further information about moving databases files: http://support.microsoft.com/support/kb/articles/q224/0/71.asp

Hope this helps,

Eric

-- Anonymous, August 11, 2004


Moderation questions? read the FAQ