There is insufficient system memory to run this query.
-
SQL 2005 Ent. Edition - 64 Bit (9.00.4035)
Running on Win 2008 R2 - 64 Bit
64 GB of Ram
SQL Configuration: 58000 MB (Max)
When we had 32 GB RAM we never got "There is insufficient system memory to run this query." error now after upgrading RAM we are experiencing issues...quite strange!
also, at times SQL Services would restart on its own.
Thought of checking with the gurus out here to see if we are missing anything.
Lock Pages in Memory -- is this an option worth trying?
------------
Below are from SQL Error Logs:
Warning: The available page file space has dropped below 4Mb.
..
..
There is insufficient system memory to run this query.
Error: 701, Severity: 17, State: 123.
..
..
Process physical/virtual memory pressure: 0/0 System physical memory pressure: 0
..
..
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 65536
-
WasimAli (8/10/2011)
Lock Pages in Memory -- is this an option worth trying?Yes, it is worth turning on. What else is running on the server? It sounds like something else may have compelled to OS to "take" memory allocated to SQL Server away from it forcefully. With "Lock Pages in Memory" turned on SQL Server will have more of a say about which memory (and if too I think) it will release memory to the OS when it asks.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Not much other than SSIS packages which does the ETL.
I will probably go ahead and apply 'lock pages in memory' and check it out how it behaves.
-
WasimAli (8/12/2011)
Not much other than SSIS packages which does the ETL.I will probably go ahead and apply 'lock pages in memory' and check it out how it behaves.
Did you solve the problem?
-
sorry, this is very old thread but I just got the same error...and we had lock paged memory turned on .....till the memory errors....
can anyone help me ?
- Robin35 - Thursday, May 4, 2017 8:17 AM
Might be better to post your own new thread on this. And include your SQL Server version, how much memory and your memory settings (min and max) as well anything in the SQL Server log related to this error (you should have more info - Look for error number 701)
Sue
- Robin35 - Thursday, May 4, 2017 8:17 AM
I don't know why people say to start another thread. They all end up in the same place... as a new post that someone can look at and the old posts have the advantage that someone may still be actively listening to the thread that has an answer.
Getting to your problem, LPIM (Lock Pages In Memory) is not a panacea of performance. It also tends to mask other problems. For Windows > 2003 and SQL Server >= 2008 R2, I recommend turning it off unless there's a very specific problem that you're trying to solve. Rather than go through all of that, please see the following old but still very relevant post on the subject by Glenn Barry.
https://sqlserverperformance.wordpress.com/2011/02/14/sql-server-and-the-lock-pages-in-memory-right-in-windows-server/--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Thursday, May 4, 2017 11:54 AM
There are several reason to start a new thread.
- You can post the thread in the forum that matches your version instead of responding in a thread for an outdated version.
- You can post the thread in a forum that is active rather than inactive. (This post is in the SQL 2005 General Discussion thread, which receives very little activity.)
- Your question will be on the first page rather than being possibly buried on page 4, 7, 20, etc., ad nauseum.
- People will be responding to your question rather than the OP's question.
- When people ask the OP questions, you are the original poster instead of someone who hasn't logged on in years.
- You can always (and should) include a link to the original thread to provide the history.
I see lots of upside for creating a new thread and lots of downside for responding on the old thread. That's why I recommend that people start their own thread.Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Thursday, May 4, 2017 12:26 PM
All true enough and I'm probably an exception rather than the rule. I look at the "latest posts" rather than by forum and rather instinctively (usually) do a couple of other things to overcome the very problems you mention on these "I have the same problem" type of questions. But, yeah, having seen your list of arguments for it, I agree. It's better to start a separate post. Thanks for the feedback, Drew.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply