Solved

there is insufficient memory in the buffer pool

Posted on 2013-06-10
11
2,383 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 9

Expert Comment

by:MattSQL
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
 
LVL 9

Accepted Solution

by:
MattSQL 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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 9

Expert Comment

by:MattSQL
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 9

Expert Comment

by:MattSQL
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 9

Expert Comment

by:MattSQL
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now