SQL Server - I changed Max Server Memory to 0 and now I can't connect to the instance

Speak guys!
In this post I would like to share with you the solution of a problem that is even common to happen and from time to time someone comes up in the Whatsapp and Telegram groups with this problem, which is incorrect setting of the Max Server Memory parameter, setting a value too low and as a result you can no longer connect to the instance:

This type of problem occurs a lot because of confusion when specifying the size and puts 128 MB thinking it is 128 GB, for example, or especially due to the value 0 in the max server memory configuration:

• If you changed the setting to 0 using the SSMS interface, it will automatically set the maximum size to 16 MB (SQL 2005-2008 R2), 64 MB (SQL 2012 32 bits) or 128 MB (SQL 2012+ 64 bits)
• If you changed the setting to 0 using T-SQL (sp_configure), it will set the maximum size to default (2.147.483.647 MB ​​= 2 Petabytes)

Solution 1 - Starting SQL in minimal mode

There are several ways to solve this problem, such as starting the SQL Server binary via command line, but I find it easier to change the service startup parameter in SQL Server Configuration Manager:

If you can't find SQL Configuration Manager on your server, just open Start Menu> Run (WinKey + R) and type:

• SQLServerManager10.msc (SQL Server 2008)
• SQLServerManager11.msc (SQL Server 2012)
• SQLServerManager12.msc (SQL Server 2014)
• SQLServerManager13.msc (SQL Server 2016)
• SQLServerManager14.msc (SQL Server 2017)
• SQLServerManager15.msc (SQL Server 2019)

Now that SQL Configuration Manager has opened, locate the problem SQL Server service and right-click Properties:

In the screen that opened, select the “Startup parameters” tab and add the -f parameter, which is used to start SQL Server with minimum settings, which ignores some SQL Server settings and memory parameters, tempdb is set to the smallest possible size, only one user can connect, and CHECKPOINT is not executed.

After adding this parameter, restart the SQL Server service:

Now open Command Prompt as Administrator:

Use SQLCMD to connect to your instance using the command “sqlcmd -S server \ instance" or "sqlcmd -S server, port"(I want to learn more about SQLCMD):

If you try to connect through SSMS, you will come across the following error message:

Login failed for user 'dirceu.resende'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

Once you were able to connect to the instance, you can now change the memory parameters:

Result:

Once you have made the change, remove the -f parameter we added and restart the service:

After restarting the service without the -f parameter, the instance resumed its normal operation:

Solution 2 - Dedicated Admin Connection (DAC)

Another (and even more practical) way is by using Dedicated Admin Connection, also known as DAC, which is a special connection to solve problems where login is no longer possible, whether it's a very low memory setting, a logon trigger preventing logins, etc. By default, the DAC connection remote is disabled, so you must enable remote DAC or you must be logged in to the server to be able to use it (local DAC).

To learn more about the DAC connection and how to enable this connection remotely, visit the article. Enabling and using dedicated remote administrator connection (DAC) in SQL Server and also the article SQL Server - How to Connect Using Dedicated Admin Connection (DAC) Connection Without SQL Browser.

To connect to the locked instance using DAC through SQLCMD, simply add the -A parameter:

In the tests I performed with low memory (128 MB), the SQL service could not even get 10s online and stopped. So I would go up the service and soon after, I was using the DAC connection to change the memory setting:

I hope you enjoyed this quick tip and that it can be helpful in your daily life.
A big hug, see you later!