6

There was a local SQL Server 2012 instance. Once memory has decreased to critical 128 MB and then has stopped by me.

I can not start it (idea: not enough memory).

Is it possible to increase this limit in config file or in system registry?

2 Answers 2

9
  1. Open SQL Server Configuration Manager
  2. In the SQL Server Services pane, locate the instance that has the problem
  3. Right-click that instance and select Properties
  4. On the Startup Parameters tab, type -f, click Add, then click Apply
  5. Right-click that instance and select Start (or Restart)

This will start the instance in minimal configuration mode (single user, no CHECKPOINT, and no startup procedures are run), which should allow you enough resources to connect and fix what you broke.

4
  • Aaron it would worth adding in answer that after starting SQL Server please change max server memory to optimum value so that next time this issue does not happens
    – Shanky
    May 13, 2015 at 16:25
  • Your answer + fast clicking (to be first) + this article: msdn.microsoft.com/en-us/library/ms178067.aspx = solution. Thanks! May 13, 2015 at 16:26
  • @Shanky That is what I meant by "fix what you broke." May 13, 2015 at 16:39
  • 1
    8 years later, and still valid answer! thank you @AaronBertrand
    – bob mason
    May 10, 2023 at 18:26
0

Confirmed this can still occur in SQL 2019, so this post is still relevant.

Aaron's answer is correct, but there are situations where changing the setting back using Management Studio isn't possible or practical; in which case you may have to do so at the command line via sqlcmd. Here are those steps, thanks largely to this answer by spaghettidba and this blog post by Marek Maśko:

  1. Add -f to your SQL startup parameters, per Aaron's answer.
  2. If you have applications attempting to grab that single admin connection on startup, either stop those applications or add -m"sqlcmd" to the startup parameters; this will ensure only your connection will succeed.
  3. Stop and start the SQL Server service; do not start the SQL Agent.
  4. Open a cmd prompt
  5. Type sqlcmd to connect to the default local instance, or use the -S parameter to connect to a named server and/or instance: sqlcmd -S myserver\SQL2
  6. At the sqlcmd prompt, enter the following commands (the expected results are also shown; note the sp_configure options are case-sensitive):
1> exec sp_configure 'show advanced options', 1
2> GO
Configuration option 'show advanced options' changed from 0 to 1. Run the
RECONFIGURE statement to install.
1> RECONFIGURE
2> GO
1> EXEC sp_configure 'max server memory (MB)', 16000
2> GO
Configuration option 'max server memory (MB)' changed from 128 to 16000. Run the
RECONFIGURE statement to install.
1> RECONFIGURE
2> GO
1> QUIT
  1. Stop the SQL Server Service
  2. Remove the startup parameters you added in SQL Configuration Manager
  3. Restart SQL services and verify your memory settings look correct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.