Classic GUI issue or how to fix MAX Server memory too low

The issue

I played today with my local SQL Server 2019 instance, and by mistake, set a MAX Server memory to 128MB.

Actually, I have set it to 10MB, but "something" changed it and it was saved as 128MB. That still too low ...

I changed a few things and didn't want to change the memory setting. It took to me a moment, to understand what happened. Yea…. classic GUI issue... 🤦‍♂️

From the "click Ok" moment, SQL Server stopped responding, and for everything I have tried, it sent the next message:

The full message was:

===================================
Cannot connect to VITALY_LP.
===================================
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.) (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476
------------------------------
Server Name: VITALY_LP
Error Number: 233
Severity: 20
State: 0

The investigation

The SQL Server error log could not be opened, pursuant to the same previous error, and the Event Viewer shows the message:

It is important to understand, that at this specific point, I had no clue about which setting specifically changed and cause the problem.

So, I continued searching for what exactly happened. Search ... yes, but what can I check now?

Well, you better remember, even if we do not see it, SQL Server writes everything to SQL Server error log. But, I can't open it! ... is that so? Not really, we can still open the log file in a simple notepad and read what happened.

Here, this is what I found there:

2021-02-13 16:05:39.36 spid14s     The resource database build version is 15.00.2000. This is an informational message only. No user action is required.
2021-02-13 16:05:44.40 Server       Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2021-02-13 16:05:44.40 spid26s     Error: 701, Severity: 17, State: 123.
2021-02-13 16:05:44.40 spid26s     There is insufficient system memory in resource pool 'internal' to run this query.

2021-02-13 16:05:44.40 Server      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.
2021-02-13 16:05:44.41 spid6s      SQL Server shutdown has been initiated

Bingo!!

Now, let's fix it!

The fix (that failed)

By GUI we can't connect normally and change the property back. Already tried it.

Tried starting SQL service in minimal configuration (-f) or single-user mode (-m) in startup parameters ...

Login failed for user 'VITALY_LP\User'. Reason: Server is in single user mode. Only one administrator can connect at this time. (.Net SqlClient Data Provider)

The fix (that actually worked)

At this point, I decided to use SQLCMD with the next steps:

Step 1: Open the command prompt in the administrator mode (CMD > run as administrator).

Step 2: Stop the SQL Instance from running to ensure we have a clean start. Type the command net stop MSSQLSERVER and then hit the Enter button. Doing this will stop the SQL services running currently.

Step 3: Start SQL Server in the Single-User mode (/m) with minimal configuration (/f) by command net start MSSQLSERVER /f /m”SQLCMD” and then hit the Enter again.

* If not shown anything, this is fine, we are connected in the Single-User mode. This is not necessary that print any indication. Instead, will print - The SQL Server <MSSQLSERVER> service was started successfully.

Step 4: Now connect to the SQL Server. Simply type the command sqlcmd and hit Enter. Doing this will open the SQL command line.

* For named instance: sqlcmd -SServerName\InstanceName.

Step 5: Now we can fix the problem using the T-SQL (Transact-SQL) commands. Type the commands and hit Enter.

* Remember, need to use the GO command between commands, since you are directly using the T-SQL commands.

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'max server memory', 4096;  
GO  
RECONFIGURE;  
GO  

Step 6: Now exit the SQLCMD by typing the command exit and hitting the Enter button.

Step 7: Cancel the Single-User mode by restart the SQL Server in the regular mode. Type the command net stop MSSQLSERVER && net start MSSQLSERVER and then hit the Enter again.

This is how it looks like in my case

Now, my little issue was fixed! 😎

Conclusions

1. DON'T USE the GUI to make changes. No chance to remember all of the changed properties, and it is harder to investigate it afterward.

2. Use GUI to generate scripts only, and after that, execute them by using RECONFIGURE to update the running configuration value. That will protect you from mistakes.

3. Search for the SQL Server error log files and investigate them even if SQL Server can't start.

Hope my little mistake will help someone in the future!

Cheers!