Insufficient System Memory – Failed Allocate Pages

Unable to Start SQL Server

In my own local SQL Server I ran across a problem starting the SQL instance. I went to SQL Server configuration Manager and manually started the SQL Server instance. The UI showed the instance had started. I opened SSMS and tried to connect. And I waited, waited and waited some more until it didn’t connect and threw an error.

How To Locate the SQL Server Error Log

I knew I could look at the SQL Server Error Log to get more information on what had gone wrong.  I browsed to the directory where the log file is. For any system that is found by going to the drive where the binaries have been installed and go to \Program Files\Microsoft SQL Server\SQLServerVersionNumber.InstanceName\MSSQL\Log. In that location you will look for files like Errorlog, errorlog.1 through errorlog.6 The file with no number at the end is the most recent Error Log. Double click it and open in your favorite viewer. As I read through the file, I came across the below.

Insufficient System Memory – Failed Allocate Pages

Also, you can read the SQL Server Error Log using the extended stored procedure xp_readerrorlog. Here is an example that shows the memory related messages from the SQL Server Error log. Of course, you can only do that once your SQL Server is running.

xp_readerrorlog 0, 1, "memory"

Then the next thing was, how do I investigate this since I can’t start the SQL Server?

Startup Parameters for SQL Server

SQL Server has startup parameters that can be added to the SQL Server Configuration Manager to control what happens when SQL Server starts. I was looking for something that would help me get SQL Server started and let me poke around. At first I tried -m for single user mode but SQL Server still wouldn’t start. Then I found the -f parameter and it sounded like just what I needed.

“Starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode. For more information, see the description for -m that follows.”

Now that I had that information I opened SQL Server Configuration Manager. I located the SQL Server instance I couldn’t start and right clicked it. I then chose properties and typed -f in the Startup Parameters tab.

I then clicked Add to the right of the startup parameter I just added. I clock Ok and I’m prompted that I need to restart the SQL Server service to make this change effective. So, I restart SQL Server and attempt my connection again, and I’m in!

Because this was an insufficient memory error, I right clicked the name of my SQL Server instance and selected Properties then Memory. I see I only have 1024 MB assigned to the SQL instance for Max Server Memory. I increased it to 3072 MB and clicked Ok.

I went back to SQL Server Configuration Manager and removed the -f startup parameter and restarted SQL Server. Now I am able to start up SQL Server and connect with no issues.

What To Do Next

1. Go back to the link on SQL Server Startup Parameters and familiarize yourself with what is in there. You never know when you might need functionality from a startup parameter to get you past a problem.
2. Do some research on Max Server Memory.
3. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.