Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

If the temp db drive is unavailable (others system database files are available), how can I trick SQL server 2000 to create new temp db on C: and start up?

When trying to start up using sqlservr -c -f -T3608 -T4022 from command prompt, it just displays the following and thats it: alt text

Thanks in advance

share|improve this question
    
Which SQL Server version, including service pack, are you running? –  bobs Sep 7 '10 at 22:42
    
sql 2000 sp4 8.00.818 –  Manjot Sep 8 '10 at 4:49

2 Answers 2

You can run the following from a command prompt to run SQL Server in the command prompt. First, change directories to the SQL server executables folder, (possibly C:\Program Files\Microsoft SQL Server\MSSQL\Binn).

sqlservr -f -s "SQL_INSTANCE"

Then, if you want to permanently change the tempdb location, your can run the following SQL:

USE master
go
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'C:\<folders>\tempdb.mdf')
go
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'C:\<folder>\templog.ldf')
go

Stop the SQL Server (control-c) in the command prompt and start the SQL Server service as normal.

share|improve this answer
    
I am trying to use sqlservr -c -f -T3608 -T4022 but it isnt starting up. I know the location of old tempdb files and I have created their directories. SQL has created the temp db files but still it hasn't started up –  Manjot Sep 7 '10 at 21:06
    
I forgot to mention that the command window will show the sqlservr command as running and will not return the command prompt. Is that what you see? If yes, have you tried to use SQL Server Management Studio to try to connect to the SQL Server at that point? –  bobs Sep 7 '10 at 22:23
    
Yes, but the Service is not up :-( –  Manjot Sep 8 '10 at 3:29
    
I guess we can't avoid the error message in the error log then :) You can start here support.microsoft.com/kb/319892 for information. –  bobs Sep 8 '10 at 17:39

Use this code: you will need to know the logical names of the tempdb database files. The logical name for each file is in the NAME column

USE tempdb
GO
EXEC sp_helpfile

Change the location of each file using ALTER DATABASE.

USE master
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdb, FILENAME = 'C:\tempdb\tempdb.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdblog,FILENAME = 'C:\tempdb\templog.ldf')
GO

Stop and restart SQL Server.

share|improve this answer
    
This works under the assumption that the SQL Service can start. Without access to the tempdb the database won't be able to start to you can't run T-SQL. –  mrdenny Sep 7 '10 at 8:30
    
the fact that it wouldn't start wasn't part of the question when I answered it. –  Jim B Sep 8 '10 at 11:47
    
I am really sorry for the confusion. Appologies... –  Manjot Sep 9 '10 at 2:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.