Solved

there is insufficient memory in the buffer pool

Posted on 2013-06-10
11
2,789 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
LVL 10

Accepted Solution

by:
Matt Bowler earned 500 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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

726 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