Skip navigation

I've put tempdb in ram and now I can't restart my SQL Server.

A. This is because the memory allocated by SQL Server does NOT include tempdb in ram. There must be enough memory for SQL, tempdb AND NT otherwise SQL will fail to start. To recover from this :-

1. Stop SQL Server and SQL Executive. Also make sure that SQL Enterprise Manager isn't running.
2. Go to the <sql>\binn directory and type "sqlservr -c -f" - this will start SQL in single-user mode with a minimum config.
3. Ignore the text messages in this window - but wait for them to finish appearing (shouldn't take more than 10-20 seconds)
4. SQL Server is now started.
5. Go to another window and start ISQL/W and connect locally with the sa userid.
6. In ISQL/W issue the following commands :-

sp_configure tempdb, 0

7. Now go back to the window SQL is running in and type "shutdown" and enter
8. SQL Server should shut down. If it doesn't then hit <ctrl-c> to shut it down.
9. Now you should be able to start SQL normally and connect. You will need to re-size tempdb as it will have gone back to the original 2Mb in master.

If you get an error 615 running the sp_configure command then drop the tempdb device first :- "sp_dropdevice tempdb" - and then start again.

TAGS: SQL Server
Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.