Search
Home
Articles
Backup
Books
Certification
FAQ
Products
Replication
Scripts
Seminars
Training
TSQL
MSDN Fourms
Philippine SSUG
Fort Worth SSUG
Oklahoma City SSDG
Resume
MHS Enterprises
BlowFrog Software
FilAm Software
AcrylicAcetate.com
Bargain Humidors
Western Humidor

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

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

Michael R. Hotek

All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.