Max Server Memory Setting

This morning I was under the influence of one of those “what happens if I do this” moments. In this case, it was “what happens if I set the max server memory setting to 0”? I learned two things very quickly. First, I noticed that SSMS would reset the 0 to be 16, which is the minimum setting allowed for the max server memory. Second, I learned that changing your max server memory setting to 16MB can cause it to become unresponsive. Very unresponsive. To the point I needed to reboot my laptop in order to continue working.

Sure, I could have waited, and perhaps a reboot is not necessary in order to connect to the instance again. But I needed to speed things along and didn’t want to wait.

Anyway, as my laptop was rebooting I went from “what happens if I do this” to “oh shit, that’s what happened”, and then to “what will I do if the instance won’t start”? I quickly found this article out on MSDN that explains the use of the -f switch should I need to reset the max server memory value. Thankfully the instance started just fine for me to connect and reset the value using SSMS.

But it hammered home a few things for me. First, breaking things is a great way to learn, just ask any child. Second, you don’t want to break things that need to remain stable, like a production server. If you do, then you will probably want to perform a quick URLT (Update Resume, Leave Town).