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
Solved

Error "Insufficient system memory" in SQL 2008

Posted on 2010-08-23
14
357 Views
Last Modified: 2012-05-10
Getting the following error when opening a large view in SQL 2008.  Worked fine in 2000.  "There is insufficient system memory in resource pool 'internal' to run this query.".  Any ideas what would cause this and how to resolve?

Thanks!
0
Comment
Question by:greymattersolutions
  • 7
  • 5
  • 2
14 Comments
 
LVL 12

Expert Comment

by:mcv22
ID: 33503597
Are you at the latest SP level for SQL server 2008?
0
 

Author Comment

by:greymattersolutions
ID: 33503879
Probably not.  Is this a likely fix?  We'll apply latest sp.
0
 

Author Comment

by:greymattersolutions
ID: 33507184
Applied the latest sp and still the same error.  Can open the view with TOP 50 but get the error above 100.
0
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.

 
LVL 12

Expert Comment

by:mcv22
ID: 33507200
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 33507427
>>Any ideas what would cause this and how to resolve?<<
I am going to go out on a limb and suggest your system does not have enough memory.
0
 

Author Comment

by:greymattersolutions
ID: 33507453
Enough memory for what? To show 100 rows of a view?  Sql has 3.8 gb allocated.  It worked fine in sql 2000.  Is this a real suggestion or are you a hardware salesman :-)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33507486
>>Sql has 3.8 gb allocated.<<
And what else is running on the server?  Is this 32-bit or 64-bit?  If it is 32-bit then there are some additional settings you may need to use in order to enable more memory.  
How are you executing the query?  I trust you are not going to say from Design mode.
Can you post the exact query you are using?
If you are running this on the server, have you tried disabling intellisense, so that you do not use so much memory?

>>It worked fine in sql 2000.<<
And I am sure it "worked fine" in SQL Server 7 and SQL Server 6.5 and .. .  What is your point?  Surely you are not considering going back to a discontinued and unsupported version?

>>Is this a real suggestion <<
Not at all.  I thought perhaps you were missing the obvious.
0
 

Author Comment

by:greymattersolutions
ID: 33507499
Wouldn't be the first time, but I don't think that's it in this case.  
Thanks!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33507502
Fair enough.

Good luck.
0
 

Author Comment

by:greymattersolutions
ID: 33507741
Found an inefficient cross join in a sub view.  Seems to have caused the problem.  Caused the view to return 400,000 rows.  Still not sure why it worked in SQL 2000 and not in 2008.  Are there major differences in how these versions handle memory?  Thanks!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33507769
>>Are there major differences in how these versions handle memory?<<
Yes.  Even SQL Server 2005 was dramatically different.
0
 

Author Comment

by:greymattersolutions
ID: 33507810
Been writing commercial enterprise systems on sql server since the early 90s.  Its never boring.

Thanks for making me go back through the code.  Still seems an odd regression in execution behavior. Have a good night!
0
 

Author Closing Comment

by:greymattersolutions
ID: 33507818
Tough love
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33510230
>>Been writing commercial enterprise systems on sql server since the early 90s.<<
Than you are merely a spring chicken. :)

>>Still seems an odd regression in execution behavior. <<
Each new version of SQL Server brings with it added functionality and complexity, we cannot and should not expect something to work the same way now as it may have done with a version 10 years ago.  I will go one step further and state that there are not only queries that produce different output with SQL Server 2005/2008, but also some that do not even compile.  In all cases that I have seen that is due to a lousy query that should have been re-written in any case.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

789 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