Solved

SQL 2005: The query processor ran out of internal resources and could not produce a query plan.

Posted on 2010-08-24
6
1,328 Views
Last Modified: 2012-06-27
Hi There,

I have a simple Classic ASP recordset that inserts some data into a simple table (5 cols) (SQL 2005)

The complication in the artitecture is that the table forms part of the strucutre for about 350 indexed views (standard and full text), although only affects one of these views based on teh value of one of the columns in the table.

Im getting this error:

Microsoft SQL Native Client error '80004005'

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

If I insert the same data using a SQL insert statement via SSMS it goes in. I have also had a look at the execution plan for this query and it is quite complex and referrs back to the indexes on one of the views and a linked table via in the view, but is not that complex.

It has been working up until now.

Another random one was that there was disk space issues on this server this morning which have been resolved.

i have a hunch that an SQL restart, or server restart may solve this issue. However cannot afford the downtime right now.

URGENT advice appreciated.

Thanks.
0
Comment
Question by:jazzer102
[X]
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
  • 4
  • 2
6 Comments
 
LVL 12

Accepted Solution

by:
mcv22 earned 500 total points
ID: 33509323
What is the latest service pack applied to SQL server? Is this applicable : http://support.microsoft.com/kb/917888
0
 

Author Comment

by:jazzer102
ID: 33509362
SQL 2005 version is 9.00.4035.00 - SP3 - Developer Edition

Hotfix sounds like it should apply but they only provide it for RTM and SP1

What do you think?
0
 
LVL 12

Assisted Solution

by:mcv22
mcv22 earned 500 total points
ID: 33509441
Don't think it applies, since it was included as part of SP2 and you're already at SP3

Try upgrading to the latest cumulative update - http://support.microsoft.com/kb/2258854
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:jazzer102
ID: 33509671
Thanks for this, I have bypassed the issue but using an ADODB Command and insert statment to put the data in. This is better practice anyway.

I am a bit nervous about adding the above hotfixes when they dont directly relate to the issue. And cant afford any downtime at this point.

Also Im becoming increasingly suspicious of MS service packs that "include" previous hotfixes for errors that happen exactly as the hotfix describes, but shouldnt happen as you have as SP that "covers" the issue. This is not the first time this I have come accross this situation within SQL 2005.
0
 

Author Closing Comment

by:jazzer102
ID: 33510150
This seems like it should be the best fix for the problem, also see my final commnet regarding my workaround.
0
 

Author Comment

by:jazzer102
ID: 33520834
Further Info:

Tried to resolve by installing latest Cumalative Update and this does NOT resolve the issue.

Cant install the hotfix in the first reply as on SP3 and this is only for SP1
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

734 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