Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

there is insufficient memory in the buffer pool

Posted on 2013-06-10
11
Medium Priority
?
3,347 Views
Last Modified: 2013-06-11
I sometimes get the following message from my VB6 application.  My database server rests on a Windows 2003 SQL 2008 Server

there is insufficient memory in the buffer pool

What is exactly happening and what can I do to fix the problem?
0
Comment
Question by:al4629740
  • 5
  • 5
11 Comments
 
LVL 14

Expert Comment

by:Don Thomson
ID: 39235489
If it's happening during synchronization  you may want to look at this

http://www.red-gate.com/messageboard/viewtopic.php?p=13262#13262
0
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 39236672
Essentially there is a shortage of memory available to SQL Server to complete an operation.

http://msdn.microsoft.com/en-us/library/aa337354(v=sql.90).aspx

It would help to understand what is happening on the server at the time this error appears.

also:

-What is SQL Server's max server memory set to?
-What is the physical memory available on the server?
-32 or 64 bit?
-Any other memory intensive processes running?
0
 

Author Comment

by:al4629740
ID: 39236843
where do I find the sql server memory setting?

Server has 4GB RAM

32 bit

Carbonite runs in the evening
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 10

Accepted Solution

by:
Matt Bowler earned 2000 total points
ID: 39236906
In SQL Management studio run:

select * from sys.configurations
where name = 'max server memory (MB)'

It looks like you have memory pressure. With 32 bit the first thing to investigate would be the 3GB switch.

http://technet.microsoft.com/en-us/library/bb124810(v=exchg.65).aspx

Beyond that, if you are able to add more memory, you could look at AWE.

http://msdn.microsoft.com/en-us/library/windows/desktop/aa366796(v=vs.85).aspx
http://msdn.microsoft.com/en-us/library/windows/desktop/aa366527(v=vs.85).aspx

There is advise on the web around clearing out caches to temporarily free up memory:

http://msdn.microsoft.com/en-us/library/aa337354(v=sql.90).aspx

This could work as a short term solution. But I would use as a last resort. Basically if your server needs more memory - give it more memory.
0
 

Author Comment

by:al4629740
ID: 39237816
What exactly is the 3 GB switch?
0
 

Author Comment

by:al4629740
ID: 39237911
here is what i got

configuration_id	name	value	minimum	maximum	value_in_use	description	is_dynamic	is_advanced
1544	max server memory (MB)	2147483647	16	2147483647	2147483647	Maximum size of server memory (MB)	1	1

Open in new window

0
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 39239310
Okay - so SQL Server is configured to use the default, which means consume as much memory as needed. On a 4GB machine without the 3GB switch this is capped at around 2GB.

The error suggests that at times this is not enough.

On your server the default memory setup is to reserve 2GB for user mode processes and reserve 2GB for the OS. Booting with the 3GB switch allows user processes to use 3GB of the memory (accurately called the Virtual Address Space). This will basically allow SQL Server to claim an extra GB for the buffer pool.

This all ignores the application carbonite that you say is also running. I'm not familiar with that application so can't comment on it's memory usage.
0
 

Author Comment

by:al4629740
ID: 39239621
If I upgrade the server to have 16GB of ram does that mean that I will have to manually set the user mode process to more than 2gb?  With so much ram how much should I set it to?
0
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 39239658
If you upgrade to 16GB you won't need to worry about the 3GB switch - because you'll have 14GB of user accessible memory.

BUT, because you have a 32 bit system you will need to do a few other things so that you can access the extra memory...

http://blogs.technet.com/b/beatrice/archive/2008/08/29/3gb-pae-and-awe-taking-away-some-confusion.aspx
0
 

Author Comment

by:al4629740
ID: 39239727
Is it better to go 64 bit?
0
 
LVL 10

Expert Comment

by:Matt Bowler
ID: 39239730
Certainly makes life easier when it comes to memory management.

As of SQL Server 2012 - 32 bit is no longer supported.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question