One of my clients contacted me for whom I assisted incomplete installation of SQL Server along with health check and backup automation. Here is the email which came across about max server memory.
Hi Pinal,
Please help! This is urgent as SQL Server is down. This is the one which you set up for us.
I was in SQL Server Management studio and I was going to put the memory allotted to 145000 MB and instead, I clicked OK when it was 14 MB. After that, I was unable to connect. I thought restart would help but now SQL Service is unable to start.
Quick help would be appreciated.
Thanks.
<Name Hidden>
Within 5 minutes I was online with them. Here is the error if you simulate the same scenario by setting SQL memory to the lowest value.
Max Server Memory
Cannot connect to localhost.
——————————
ADDITIONAL INFORMATION:
The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
——————————
No process is on the other end of the pipe
——————————
When I looked at ERRORLOG, here were the few lines about Max Server Memory.
- Error: 701, Severity: 17, State: 65.
- There is insufficient system memory in resource pool ‘internal’ to run this query.
- Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
- Error: 17300, Severity: 16, State: 1.
- SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option ‘user connections’ to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.
- SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
The out of memory error (701: There is insufficient system memory in resource pool ‘internal’ to run this query.) is clearly side effect of what they have done by mistake. At this point, we need to start SQL and reconfigure memory to the value which they wanted to set.
WORKAROUND/SOLUTION
In this situation where SQL is not starting due to incorrect memory setting, we have a way to start SQL Service in minimal configuration using a parameter called as f. Here are the steps.
If SQL is running and you are not able to connect then first stop SQL Service. You can do it via command prompt, services applet or SQL Server Configuration Manager. There are chances that SQL Service might not stop then you may want to kill respective sqlservr.exe from task manager.
- Once SQL is stopped, we need to start with f parameter which stands for minimal configuration. There are multiple ways to do it and I would explain the easiest way.
- Open command prompt (Run as Administrator).
- For default instance run below:
NET START MSSQLSERVER /f /mSQLCMD
For named instance run below:
NET START MSSQL$INSTANCENAME /f /mSQLCMD
Make sure SQLCMD is in upper case.
SQL Service should start.
- Connect to SQL using SQLCMD. Note that SSMS and other tool won’t work as we have allowed connection via only SQLCMD using /m parameter. This is to ensure that the application is not taking connection before us.
- Check max server memory using below command.
sp_configure 'max server memory'
you should notice a low value in config_value column.
- To reset the value to your desired value (in MB), run below command. I have put value like 12000 which is equal to 12 GB.
sp_configure 'max server memory', 12000
- Run reconfigure command.
reconfigure with override
- Verify that value set in step 4 is visible in the output. Same command as step 3.
sp_configure 'max server memory'
- Once you are satisfied “exit” from the SQLCMD. Now stop SQL Service as its running with special parameters.
- For default instance run below:
NET STOP MSSQLSERVER - For named instance run below:
NET STOP MSSQL$INSTANCENAME
SQL Service should stop.
- Now you are free to start SQL using any method which is easier for you. If its cluster then you need to bring SQL resource online via Failover Cluster Manager. Here is the command to start SQL from the command prompt. This is same as Step 1 but without any extra parameter.
- For default instance run below:
NET START MSSQLSERVER - For named instance run below:
NET START MSSQL$INSTANCENAME
SQL Service should start.
Here is the pictorial representation of steps.
Hope this article would help someone who is in trouble and wants to fix the issue ASAP. Please provide feedback via the comments section. SQL SERVER –Start Stop Restart SQL Server From Command Prompt
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
I have tried this at a dev server for learning purposes and i came accross 2 things. I configured the max server memory via the GUI with 14 MB, closed SSMS and tried to re-connect.. didn’t work. Great, this is what we want for this test.
1. I start the service as mentioned, connected and used sp_configure to request the max memory values. I see the config_value set to 128 and run_value to 2147483647. I would expect the run_value to apply, not the config_value. Technet says ‘run_value – Currently running value of the configuration option’. So I would assume that the running value is 2147483647, so the service would start.. it doesn’t of course. I have tried to look it up, but cannot find a real clear anwser for this.
2. Changing the value in SQLCMD and querying the values after the change gives me the following output: ‘Msg 15123, Level 16, State 1, Server , Procedure sp_configure, Line 62 The configuration option ‘max server memory’ does not exist, or it may be an advanced option.’. Turning advanced options on again fixed this. Seems it is somehow changed in the process. Maybe because we use reconfigure with override.
Hi Pinal, thank you for sharing your knowledge.
I know this is an old post, but I have a somehow related question.
I’m not an expert dba by any means but I’ve found myself needing to provide some degree of expertice on this matter to our customers and I’ve been reading a lot, and your blog is one of those that pop up on the Google search very often.
I’ve a question marginarily related to this blog post. This week I’ve tried to help two customers with performance issues; the SQL Server engines used are 2017 and 2019 respectively. One common detail is that when I read sys.dm_os_performance_counters to check de max, total and target memory, on those two customers I’ve seen a 0 returned as de max memory value; SQL is running well, more or less, and I don’t find anything about having a relatively high value set on the properties of the SQL server instance, but at the same time having 0 being shown on that dynamic view. Ive tried to reconfigure the value with tsql but it has clearly reported back that it was setting the same value that was previosly set.
Being that max memory points to the max memory used by the buffer pool, and that SQL server is working relatively fine, I believe that this could be an OS or SQL Server error of some kind.
Have you encountered this problem? Do you have any explanation about it?
Thanks a lot.
Faced exactly same issue.
Thanks for the article. It saved a lot of time.
Thank you!!! It was only a local developer instance that I broke, but this was super helpful!
This just saved my life. Other suggestions on the internet didn’t work, but your detailed descriptions worked flawlessly, and I owe you a drink Mr. Dave.
Helped me a lot! Thanks