[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

there is insufficient memory in the buffer pool

Posted on 2013-06-10
11
Medium Priority
?
3,211 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

650 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