I've overallocated memory for SQL Server and now it won't start - what can I do?

A. 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 memory, <new memory value>
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. 


Hide comments

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