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
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.

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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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 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.


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.