moving tempdb.mdf in SQL 2000

[NickN]

I need to move the tempdb.mdf to another drive. Right now it is on my C drive (RAID5 setup) and is taking up way too much space. I had moved my main database to another drive upon creating it but I failed to take into account the tempdb growth during processing. I do not want to re-create my database if I don't have to. I am not sure how to move this tempdb to another drive without reinstalling - any assistance would be great!


Thanks in advance.

 

[tlbroadbent]

Which version of SQL Server are you running? Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[NickN]

SQL Server 2000 sp 2

 

[tlbroadbent]

Use the following script in Query Analyzer. Then Stop and Restart SQL Server.

-- Replace the filename with the location
-- where you want to place tempdb

alter database tempdb
modify file (name='tempdev',
filename= 'E:\MSSQL\Data\tempdb.MDF')
alter database tempdb
modify file (name='templog',
filename='E:\MSSQL\Data\templog.LDF') Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[NickN]

Where you have: (name='tempdev', . . . do I leave 'tempdev' as 'tempdev' or is this the name of the file I have on my server?

Thanks so much for you help.

 

[tlbroadbent]

Tempdev is the logical name of the DB device not the physical name of the file. Examine the properties of TempDB and look at Data Files and Transaction Log tabs to confirm the names are correct. You can alos run the following query in QA to list the device names.

Select name, filename From tempdb..sysfiles

NOTE: Name is the logical device. Filename is the physical file name.

Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[NickN]

It worked! Thanks so much. Can I use this same script on the msdbdata.mdf and msdblog.ldf and master.mdf and mastlog.ldf?

Thanks again!!!

 

[tlbroadbent]

No. The procedures to move msdb and master are different. See the article at...

http://support.microsoft.com/support/kb/articles/Q224/0/71.ASP

Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[NickN]

Thanks. Are there any SQL server 2000 dba books you recommend?

 

[tlbroadbent]

I own the following two books and find them extremely helpful.

Inside Microsoft SQL Server 2000
by Kalen Delaney

The Guru's Guide to Transact-SQL
by Kenneth W. Henderson

I don't own the following book but may buy it soon. It has recieved excellent reviews.

Admin911: SQL Server 2000
by Brian Knight Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm