Increase memory limit when server is stopped

Question

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?

score 9 · Accepted Answer · answered May 13 '15 at 14:56

9

Open SQL Server Configuration Manager
In the SQL Server Services pane, locate the instance that has the problem
Right-click that instance and select Properties
On the Startup Parameters tab, type -f, click Add, then click Apply
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.

answered May 13 '15 at 14:56

Aaron Bertrand

180,303
28
400
614

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 '15 at 16:25
Your answer + fast clicking (to be first) + this article: https://msdn.microsoft.com/en-us/library/ms178067.aspx = solution.
Thanks!
– Aleksandro M Granda May 13 '15 at 16:26
@Shanky That is what I meant by "fix what you broke." – Aaron Bertrand May 13 '15 at 16:39
1

8 years later, and still valid answer! thank you @AaronBertrand – bob mason May 10 '23 at 18:26

BradC · Answer 2 · 2023-06-12T20:36:14.833

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:

Add -f to your SQL startup parameters, per Aaron's answer.
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.
Stop and start the SQL Server service; do not start the SQL Agent.
Open a cmd prompt
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
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

Stop the SQL Server Service
Remove the startup parameters you added in SQL Configuration Manager
Restart SQL services and verify your memory settings look correct.

Increase memory limit when server is stopped

2 Answers2