Bypass recovery mode

Whenever I try to start the SQL2000 sevice, I get the error "table tempdb in bypass recovery mode" and the process is terminated... It started when we first received the message that the transaction log for the 'tempdb' database was full although it was set to grow and ample space was availiable (2GB).  We created a second log and this message appeared... HELP!!!

Gene
gene_jmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arbertCommented:
It should be as easy as deleting the tempdb.mdf and the ldf file--SQL Server will recreate them on startup.  If SQL Server doesn't want to recreate them, take a look at traceflags (3607, 3608 and 3609)

Brett
0
gene_jmAuthor Commented:
Hi Brett

The files were deleted ....and SQL2000 recreated them, but then the services stopped showing the same error "Could not run begin transaction in database 'tempdb' because the database is in bypass recovery mode"
0
gene_jmAuthor Commented:
based on the above  the service cannot be started....therefore how does one one look at traceflags (3607, 3608 and 3609)
gene_jm
0
arbertCommented:
You apply a trace flag when you start sql server--if you look at the service property, you can add /t startup option--see the options below....It's weird that it recreated tempdb and then gave a recovery error--did you try moving the files or something?  What happened to make the recovery mode kick in?

http://www.transactsql.com/Articles/startupparms.html

http://www.databasejournal.com/features/mssql/article.php/1442191#part_2


Brett
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.