Unable to connect to SQL servers Error 17300

I recently worked on issue in which we were unable to connect to the SQL instance and we were getting below error while we try to connect  even though the SQL service was running

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)

While analyzing the  error log I was able to find error 17300 which points to memory pressure , but the instance  was a newly provisioned one and there was no chance for any workloads causing memory pressure and while analyzing the logs further I found that while doing the post install tasks Junior DBA accidentally set  max memory to a very small value 

OK.. So now we know what change caused the issue and max memory need to be increased to fix the issue  . How can we increase the max memory where we are not able to connect to instance ?

These are the situations in which Dedicated Administrator Connections comes as savior

I took the DAC session by giving 'admin:Instnace name' in the connection window which popups while i open a new query window[Ctrl +N] in SSMS( if you try to take DAC connection by connecting to instance from object explorer it will fail with the message :"Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design" as object explorer make multiple connection to instance) another option to connect to DAC is using SQLCMD.

 

Once you have DAC connection run the command to change the max memory setting 

EXEC sp_configure 'show advanced option', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max server memory', '10240'
GO
RECONFIGURE WITH OVERRIDE
GO 

After increasing max memory setting  normal SQL connection was working fine but i have an interesting question from my curios Junior DBA " what if he would have tried to restart the instance and instance failed to start due to lack of memory? " 

So let me show you how we can resolve such situation. I changed the max memory back to 128 MB and restarted the instance and  the instance failed to start and as expected there was 17300 logged in the error log

So how to change max memory setting now  as DAC is not an option when the SQL itself is down 😝

We still have a Savior "Minimal configuration Mode" which helps us when the SQL fails to start due to configuration issue , we can start Sql service in minimal configuration mode by giving -f in startup parameter or running "net start MSSQLSERVER /f" from an elevated command prompt.

Once the SQL Service was started in minimal configuration mode we took the DAC connection and changed the memory setting ,stopped and restarted the service in normal mode and now were are able to connect to SQL instance.