Jeff Prom's SQL Server Blog

Sharing knowledge and tips on SQL Server, Business Intelligence and Data Management.

Can’t Start SQL Server Due to Insufficient Memory

Posted by jeffprom on December 14, 2017

SQL Server loves memory and will take as much as you give it. Sometimes you may want to limit the amount of memory it can have. To do this, go into management studio, right click on the server, select properties, and select the memory tab. To limit the amount of memory it can have, set a value for Maximum Server Memory (in MB).

If you aren’t careful, you can set a value that is too low and SQL Server won’t be able to run. I recently did this on my local copy and had a hard time changing the setting again to a higher value as everything expects that SQL Server is running, and that you can simply change the configuration.

In my case, I had set the value to 128 MB and SQL Server kept showing an “insufficient memory” error in the windows event log and wouldn’t start.

If you find yourself in a similar predicament, here is how to get it working again.

Open a command prompt as an administrator. Find the install path of your version of the SQL Server engine. In my case it was “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn”.

Change to the directory:

Cd “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn”

Next, we want to start SQL Server with minimal configuration. This will start SQL Server in a minimal state and allow you to connect in management studio. To do this use the following:

Sqlservr.exe -f -s <instancename>

Open the server properties window and bump up the max memory limit.

Close the command prompt window to shut down SQL Server. Finally, go to SQL Server Configuration Manager and start the service like normal.

You should now be able to run the service as usual and connect.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: