SQL Server Version:
SELECT @@VERSION; /* Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) */
So, I was playing around with max memory and I accidentally set max memory to 128MB and now, I cannot open a connection to SQL Server. When I tried to open a session and fix the mistake, I got this wonderful pop-up.
In order to save myself, I decided to add the -f parameter to SQL Server and bounce the service.
See more about that here:
Once the service was re-started, I tried once again to connect. I got the following message:
This was expected according to the information I had about the -f flag. I then was able to open a connection using the Dedicated Admin Connection (DAC). I was then able to run sp_configure and alter the max memory setting. Here is what I saw in sp_configure before I made a change. It appears that the running value is again infinity (or close enough).
I ran this update to increase the max memory to 3GB:
USE master; GO sp_configure 'max server memory (MB)' , 3072; GO RECONFIGURE; GO
And then I removed the -f parameter and re-started SQL Server. At that point, the server became accessible in multi-user mode and I had a max memory value equal to 3GB. This is not the most elegant solution I have ever encountered; but, alas it worked; so I decided to share it here.
Does someone have a quicker and cleaner solution?