start up parameter -f - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

start up parameter -f

Author

Topic

Crotalus
Starting Member

16 Posts

Posted - 2007-08-07 : 10:15:27

I have my sql server set to start whenever my computer turns on. I recently moved my tempdb to another drive and now it won't start at all. I have posted in many forums for help and so far have found nothing that helps at all. My error message in the log says to start sql with the -f parameter. This means absolutely nothing to me. I have spent hours searching the web for any kind of help on this and have found nothing that shows how to do this. I find it unbelievable, that the simple act of moving a database can cause this whole program to cease functioning. And to top it off there is nothing written anywhere on how to fix it. Any help would be greatly appreciated.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-07 : 10:25:26

You will have to start the server from the command line

Which version of sql server
open a command windon and run
SQLSERVR.EXE -f

You might have to find where the exe is.
Once it is started you should be able to change the location of tempdb.

>> I find it unbelievable, that the simple act of moving a database can cause this whole program to cease functioning

Well it's a system database which is needed for anything to run.
Hw did you move it? tempdb is recraeted every tinme the system starts up so it sounds like it thinks it's on a non-existant folder.

And it's not true there's nothing written about it

http://support.microsoft.com/kb/173090
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=108800&SiteID=1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Crotalus
Starting Member

16 Posts

Posted - 2007-08-07 : 11:01:14

Thanks for the reply. I am using sql server 2000. I used this code to move the database:

USE master
go

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\tempdb.mdf')
go

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\templog.ldf')
go

My error log says (among other things): "tempdb.mdf is not a primary database file."

The links you posted don't really mention anything about that or about how to use the -f parameter.

But thanks for your help. Using that -f parameter, I can get into Enterprise Manager. Not sure what I am going to do now, but it is a start. And the command prompt shows "starting up database ...." over and over again for the same databases. Not sure what that is all about either?!?!

Ron

Crotalus
Starting Member

16 Posts

Posted - 2007-08-07 : 11:20:41

No luck so far. I really couldn't do anything in enterprise manager. I got a message saying I couldn't connect to the server or something like that. Also, the command prompt started saying:

"Login failed for user 'admin'. Reason: Server is in single user mode. Only one administrator can connect at a time."

Of course, I am only logged on as one user and the command prompt already said that user was logged on and trusted. Why it wants to try to log on again and then fail is beyond me.

Also, if I delete the tempdb.mdf file SQL creates a new one. If it can do that, you would think that it would create one that it can use. But no, it makes it and then says it doesn't like it!

Ron

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-07 : 11:23:25

You woun't be able to do much using enterprise manager but you should be able to do things witg query analyser.

Run select * from master..sysaltfiles to see what the tempdb values are set to.
(I take it you have an E:\ drive and the sql serevr servbice account has access to it?)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-07 : 11:25:14

As it's creating it it must have access.
Can you set it back to the old files (from query analyser).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-07 : 12:51:17

You don't need start sql in single user mode to alter tempdb file path.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-08-07 : 13:20:37

The problem is that sql server won't start (probably) due to an invalid tempdb.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Crotalus
Starting Member

16 Posts

Posted - 2007-08-07 : 13:54:29

I can't do anything in query analyzer either. I get the error message "Unable to connect to server...:" and "Only one administrator can connect at this time." And the command prompt continues to log in and fail after first succeeding, endlessly.

Crotalus
Starting Member

16 Posts

Posted - 2007-08-07 : 14:15:56

If I start up query analyzer after the sqlservr.exe -f command successfully logs in and BEFORE it fails to log in, I can get in and do things. I used "select * from master..sysaltfiles" to look at the tempdb. The filename is set to the proper location.

When sql server starts up it does create the tempdb file in the proper location, it just says that it is not a primary database and then it shuts down. How can I make tempdb a primary database?

Crotalus
Starting Member

16 Posts

Posted - 2007-08-07 : 14:22:21

Looks like I got it going, thanks to your help! Looking at the master..sysaltfiles table in query analyzer, I noticed that the templog file had the exact same filename as the tempdb! This is what caused all of my problems. The keys to correcting the problem were:

1. "sqlservr.exe -f" to get the server started so I could use query analyzer.
2. "select * from master..sysaltfiles" to look at the settings for the tempdb.
3. After that I used an update query to change the filename of the log file.

Thanks a lot nr! I learned a lot today.

Ron

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-07 : 14:54:32

You said you ran this:


USE master
go

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\tempdb.mdf')
go

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\templog.ldf')
go

So was what you posted not correct? You must've had the same FILENAME for both.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Crotalus
Starting Member

16 Posts

Posted - 2007-08-07 : 15:27:50

Yep, what I posted was not correct. That was the sample code I found on the internet. The actual code I used was wrong. My SQL computer isn't the computer I use to access the internet so it was easier to use the sample code I found on the internet rather than the actual code. So, yes, I screwed it up! But it was still a valuable lesson for me on temp databases and hopefully it will help people with similar problems in the future!

Ron

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources