Got an error starting SQL Server stating there is not enough memory. This happened after there was a change to increase the SQL Memory size. My first impression was someone made an error, placed a decimal on the wrong spot or confused MB with GB. And so it was.
To resolve it’s quite easy, start the SQL Server in minimal mode and configure the correct max memory value:
- Stop the SQL server (be sure pick to the right instance)
- Change to the SQL Server installation folder.
- Usually something like this: C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn
- Start the SQL server with minimak configuration:
- Sqlservr.exe -f -s <instancename>
- Now you can start the SQL Server Management Studio again and fix the error from the GUI.
- Alternative: you can use SQLCMD to set the max memory correctly to 32GB:
- net stop MSSQLSERVER
- net start MSSQLSERVER /f /mSQLCMD
- sqlcmd -S localhost -E
- sp_configure ‘max server memory’,32768
- reconfigure with override
- go
- exit
- net stop MSSQLSERVER
- net start MSSQLSERVER
- Alternative: you can use SQLCMD to set the max memory correctly to 32GB:
- Stop the SQL Server and start it again normally