• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

Recovering tempdb

I have a serious problem with SQLServer 6.5. The problem is that when I was trying to use a SELECT query over a big table, SQLServer raises an error that said the tempdb has no more space left.

So, I decided to create another device and expand the tempdb in the new device. I did it, but the data
section of the database continued reporting just 2 MB of space and the log has increased its size in the amount of the new database's size. For that reason, the query didn't work too.

Then I decided to put the tempdb in RAM rather that in a hard drive. I did it, or at least, I think I did it. The problem is that I'm no longer to load de MSSQLExecute, in the NT event log it says that there is a problem of login, it is: Error 1069 (The service did not start due to a login failure) ocurred while perfoming this sercice operation in the SQL Executive service.

The  I tried to get the tempdb back to a hard drive with the following sentences:
sp_configure 'tempdb in RAM',0
reconfigure with override

But, it doesn't work, what really catches my attention is the fact that there is no tempdb.dat in the DAT directory, so, I don't know if I did something that could have dropped the database.

Is there any way to recreate the tempdb or load it into RAM memory and let SQL server to reconstruct all the information of the installed databases?

If not, Is there any way to create a backup from the
  • 3
  • 2
  • 2
1 Solution
The tempdb database is on master device by default. So there are no
file tempdb.dat in the DAT directory. If you have problem with
starting SQL services, read about
"I've put tempdb in ram and now I can't restart my SQL Server"

I recommend you to remove the tempdb database from master to new device.

1. Create new device with size of 30-40Mb (tempdb for example)
2. Uncheck "Default device" option for master device.
3. Set "Default device" option for tempdb device.
4. From Enterprise manager (or sp_configure) set tempdb to be in RAM (set value to 1)
5. Stop and restart MSSQLServer service.
6. From Enterprise manager (or sp_configure) set tempdb to not be in RAM (set value to 0)
7. Stop and restart MSSQLServer service.
8. Expand tempdb database to the full size of tempdb device.

It must solve your problem.
GustavoValdesAuthor Commented:
Hi chigrik's, I tried to set the tempdb to run in ram but I've got two problems with that:
  1. I can not start the SQL Enterprise
  2. If I load the SQL server in single
     user mode and try to execute the
     following line:
     sp_configure 'Tempdb in RAM',1
     It raises this error:
     Msg 615, Level 21, State 1
     'Unable to find database id=2    
      name = tempdb'

Another problem that it reports is that it can't find virtual page24 for the tempdb database.
I am afraid, if you received this error then you must rebuild your database.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

GustavoValdesAuthor Commented:
You are wright in this last comment, chiqrik, indeed, I had to reinstall SQLServer because every way I tried to fix the tempdb it said that the 24 virtual page was not found...

Fortunately, I've found the way to rebuild the database from the .DAT files in the MSSQL\DATA directory.

Thanks anyway, chigrik.
2 things:

1) If you put tempdb in RAM, make sure you have a lot of memory, because it's stealing memory from MS-SQL (it's not shared). Say you have 256 MB on your NT server and you make a Tempdb of 128 MB. This leaves 128 Mb for NT and SQL.
If you assign too much memory to tempdb and you can't restart SQL serveror get this error, start SQL server in a 'safe mode' I don't have the manuals in front of me, but there's a flag.

2) Tempdb is cleaned every time you start SQL server, so you can't get any data back in Tempdb.

And, as always, write down your configuration on a piece of paper, or check http://www.swynk.com for some useful scripts.
GustavoValdesAuthor Commented:
I reject your answer not because it's not right, it's just because I have already fixed the problem but I couldn't find the way to close the question so anybody else answer it.

Thanks anyway.
If you're showing me a 200 points question and I'll take the time to answer your question, it's not fair to react this way. Either accept the answer (it's correct like you said), or make sure you know how to remove/close your question. Getting points is not my goal (I don't have that much time to watch this site), but the way you handle answers is disappointing me.

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.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now