Improve company productivity with a Business Account.Sign Up

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

Error "Insufficient system memory" in SQL 2008

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
greymattersolutions
Asked:
greymattersolutions
  • 7
  • 5
  • 2
1 Solution
 
mcv22Commented:
Are you at the latest SP level for SQL server 2008?
0
 
greymattersolutionsAuthor Commented:
Probably not.  Is this a likely fix?  We'll apply latest sp.
0
 
greymattersolutionsAuthor Commented:
Applied the latest sp and still the same error.  Can open the view with TOP 50 but get the error above 100.
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
mcv22Commented:
0
 
Anthony PerkinsCommented:
>>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
 
greymattersolutionsAuthor Commented:
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
 
Anthony PerkinsCommented:
>>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
 
greymattersolutionsAuthor Commented:
Wouldn't be the first time, but I don't think that's it in this case.  
Thanks!
0
 
Anthony PerkinsCommented:
Fair enough.

Good luck.
0
 
greymattersolutionsAuthor Commented:
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
 
Anthony PerkinsCommented:
>>Are there major differences in how these versions handle memory?<<
Yes.  Even SQL Server 2005 was dramatically different.
0
 
greymattersolutionsAuthor Commented:
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
 
greymattersolutionsAuthor Commented:
Tough love
0
 
Anthony PerkinsCommented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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