Yesterday I did something stupid… I thought that I could set my SQL server memory usage back to the default (I had previously limited it) by setting the value to zero. You know, like every other such setting works in all dialogs of all applications ever written!
Well, SQL Server doesn’t work like that. Don’t do this… If you do, it will default to a value that depends on your processor. In my case, it set the new value to 128. And that leaves it with insufficient memory to login to SQL server. Setting it like this effectively makes your database unusable. Ain’t that fucking great?
Right after I did that, SQL Server Management Studio crashed, and after it restarted, I could no longer log in to my local SQL Server. A lot of people have made this mistake and I found a solution here.
The solution that worked best for me was the following:
- Stop the SQL Server service if it is running.
- Start it with the –f parameter, so that it loads with the minimum memory configuration. I did it directly from the Services settings page, after double-clicking the service name, as above.
- Now you can log in via Management Studio and set the upper memory limit to something sensible.
- All is well. Just don’t forget to stop the service, remove the –f startup parameter, and start it normally again.
At the time of writing, I’m using the latest version of SSMS, version 17.6. I still think it’s pretty dumb that it allows you to do this in the first place, but at least it’s easy to fix.