DBA

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

Advertisements

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.

 

Iā€™m currently working as a SQL server DBA in one of the top MNC. Iā€™m passionate about SQL Server And Iā€™m specialized in Administration and Performance tuning. Iā€™m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

8 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

3 + 5 =