SQL server 2012 Denali not starting there is insufficient memory available in the buffer pool

SQL server 2012 does not start

Today is not a good day. Ha ha not like that 🙂

Usual I opened SQL server DENALI to play something it doesn’t open (start) it at all. It’s throwing error. Probably you know what I will do I went configuration manager checked the service is started or not. It shows stopped. I tried to start the server but, it’s not start it. I checked the event viewer log and the SQL error log I got lots of error.

Event viewer logs:

The SQL Server (SQL2011) service terminated with service-specific error %%945.

There is insufficient memory available in the buffer pool.

SQL server error log:

2011-10-16 12:23:26.53 spid12s     The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/XXXX:1555 ] for the SQL Server service. Windows return code: 0x54b, state: 3. Failure to register an SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

2011-10-16 12:23:26.53 spid12s     SQL Server is now ready for client connections. This is an informational message; no user action is required.

2011-10-16 12:25:26.86 spid17s      Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 1

2011-10-16 12:25:26.86 spid17s     Error: 802, Severity: 17, State: 0.

2011-10-16 12:25:26.86 spid17s     there is insufficient memory available in the buffer pool.

2011-10-16 12:25:26.86 spid9s      Database ‘mssqlsystemresource’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

2011-10-16 12:25:26.86 spid9s      SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

Again probably you know what I will do. I got three different types of errors. So,

I goggled the errors and found some details from the URLs. It helped a bit.

http://support.microsoft.com/kb/909801

http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/01/22/error-945-severity-14-state-2-database-mssqlsystemresource-cannot-be-opened.aspx

I have checked the ‘mssqlsystemresource’ database it’s not in the data folder so I thought this may be a problem but my mind said 2008 onwards this database resides in ‘binn’ directory. Even though I thought its DENALI CTP3 maybe Microsoft has changed this from denail. I changed ‘mssqlsystemresource’ database location from ‘Binn’ to ‘data’ it’s not helping me.

Finally I thought to change the log on service account from ‘local system’ to ‘local services’ and ‘network services’. It’s helped me and the service started. Wait a minute…

It’s only helped me to start the SQL service when I tried to connect the server it’s not connected. Again it’s thrown error message.

I know I got more memory related errors in error log “There is insufficient memory available in the buffer pool” and finally I thought to change the max memory. And it solved my problem and SQL server DENALI connected and worked a great.

I put the SQL server to the minimal configuration mode and connected the server and changed the SQL server memory to 512 MB.

Go –> configuration manager –> right click the server –> properties –> Startup parameters –> type –ft3608 –> click add –> Ok.

You have to restart the server after adding the “–f flag”.

Now connect the SQL server DENALI with a new query. Run the following

sp_configure 'show advan',1;reconfigure
Go
Sp_configure 'max server memory (MB)','512';reconfigure

Verify the memory setting its taking or not after changing the max memory.

Sp_configure 'max server memory (MB)'

Once you have finished the max memory setting removes the ‘–f flag’ from startup and restart the server as normal.

Now you can connect the server.

Conclusion:

Don’t set the DENALI SQL server max memory to below 128 MB. I know there is no problem with SQL server 2008 but in DENALI they have changed the minimum “max server memory” limit <= 128 MB for 64 bit and <=64 MB for 32 bit.

I hope this blog post may save some folk’s time.