al4629740
asked on
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?
there is insufficient memory in the buffer pool
What is exactly happening and what can I do to fix the problem?
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?
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?
ASKER
where do I find the sql server memory setting?
Server has 4GB RAM
32 bit
Carbonite runs in the evening
Server has 4GB RAM
32 bit
Carbonite runs in the evening
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What exactly is the 3 GB switch?
ASKER
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
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.
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.
ASKER
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?
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
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
ASKER
Is it better to go 64 bit?
Certainly makes life easier when it comes to memory management.
As of SQL Server 2012 - 32 bit is no longer supported.
As of SQL Server 2012 - 32 bit is no longer supported.
http://www.red-gate.com/messageboard/viewtopic.php?p=13262#13262