[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3478
  • Last Modified:

there is insufficient memory in the buffer pool

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
al4629740
Asked:
al4629740
  • 5
  • 5
1 Solution
 
Don ThomsonCommented:
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
 
Matt BowlerDB team leadCommented:
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
 
al4629740Author Commented:
where do I find the sql server memory setting?

Server has 4GB RAM

32 bit

Carbonite runs in the evening
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Matt BowlerDB team leadCommented:
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
 
al4629740Author Commented:
What exactly is the 3 GB switch?
0
 
al4629740Author Commented:
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
 
Matt BowlerDB team leadCommented:
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
 
al4629740Author Commented:
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
 
Matt BowlerDB team leadCommented:
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
 
al4629740Author Commented:
Is it better to go 64 bit?
0
 
Matt BowlerDB team leadCommented:
Certainly makes life easier when it comes to memory management.

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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now