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

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

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

sp_configure tempdb, 0
go
reconfigure
go
Now go back to the window SQL is running in and type "shutdown" and enter
SQL Server should shut down. If it doesn't then hit <ctrl-c> to shut it down.
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.