Increase SQL server Maximum Memory from Command Prompt

You can change the min and max amount of memory allocation for each instance in SQL Server Management Studio, the defaults are:

If you accidentally set the “Maximum Server Memory” to below the amount which could run the services, you might not be able to start the SQL server service from Services or SQL Server Configuration Manager console and you will get this error if you want to log in to SSMS :

Failed to connect to server myServer. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

 

Here are the steps to increase the SQL server memory:

1. If SQL server service is stopped, open the command prompt navigate to your SQL server instance folder (mine is C:\Program Files\Microsoft SQL  Server\MSSQL11.MSSQLSERVER\MSSQL\Binn) and run this command in order to start the service with minimum requirement and single user mode :

 sqlservr -f -m -s nameofyourinstance

2.while keeping previous command prompt open, open a new command prompt as an administrator and navigate to your SQL server instance                folder (mine is: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn) and run this command to increase instance  memory to 4GB:

 sqlcmd 

 EXEC sys.sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sys.sp_configure 'min server memory', 1024;
GO
EXEC sys.sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO 

3. Now SQL server memory is increased. close those cmd windows and make sure the SQL server service is running by going to SQL Server  Configuration Manager

 

You should be able to connect to your instance through SSMS.