SQL 2005 TempDB move to another drive 2

[Ovatvvon]

Hi All,

I made a mistake and reconfigured our SQL 2005 instance on a server to have the same location as a SQL 2000 instance, which of course keeps the SQL 2005 instance from coming online after I recycled the services, which in-turn keeps me from being able to reconfigure it to the proper location.

Does anyone know if there is a way to start up the SQL 2005 instance with its default parameters? (I have not yet deleted the old TempDB files it originally had.)


-Ovatvvon :-Q

 

[ptheriault]

So if I'm unsertanding what you've done. Your 2005 install has the same tempdb location as your 2000 install?

If you can stop the 2000 instance, start the 2005 instance and run this code. (change the filename to the correct path for your environment.

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

Then restart both instances.

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

 

[Ovatvvon]

Hi Paul,

Thanks for the response. Yes, you're understanding it correctly.

Is there any way to start up the SQL 2005 install with its default settings, without having to shutdown the 2000 instance? Although this is just a Development box, there still are a lot of clients on it who have hissy-fits everytime the service goes down. If we can avoid shutting down the 2000 instance, that would be ideal.



-Ovatvvon :-Q

 

[ptheriault]

There is no way to start sql server with "default" settings. The only db that you can repoint during startup is the master db.
You could move the tempdb for the 2000 instance then it would just require a quick refresh of the 2000 services. The 2005 instance would start right up.

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

 

[mrdenny]

Try starting the SQL Service from the command line with the -m -c switches. This will load it up in single user admin mode. This may allow the server to startup without tempdb.

Your other option will be to stop the SQL 2000 instance. Start the SQL 2005 instance which should now start, and fix the location of the tempdb database. Then restart the SQL 2005 service and then start the SQL 2000 service.

Keeping the tempdb files doesn't matter. They are deleted and recreated each time SQL restarts.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Ovatvvon]

Hi all,

I needed to get this done that afternoon, so what I ended up doing was reseting the tempDB location for the 2000 instance and recycling the services. Once that was done I started up the 2005 instance and moved the tempDB location for that to its proper location. This allowed everything to work, but did cause an outage for the 2000 databases (obviously).

Denny, I will keep a note on the command line tip you mentioned and give that a try sometime on a lab box. Thanks for the info!


-Ovatvvon :-Q