Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

On a SQL Server 2005 Enterprise edition server, an index has been dropped from a view. When running a select * query that includes this view, no results are shown. In addition, the execution plan references this index on the view (that no longer exists). By adding the EXPAND VIEWS option to this query, the correct results are returned.

We have tried to clear the cache plans with DBCC freeproccache, but the problem persists (the fresh cache plan still refers to this non-existent index). We have seen this problem before, and a restart of the SQL service corrected it, but I would like to find out the actual cause before we attempt that again.

Edit: I have tried sp_refreshview, with no change. The SQL build version is 9.00.3042 (SP2).

share|improve this question

closed as off-topic by Paul White, Mark Storey-Smith, Martin Smith, Max Vernon, Phil Aug 7 '14 at 13:24

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, Mark Storey-Smith, Martin Smith, Max Vernon, Phil
If this question can be reworded to fit the rules in the help center, please edit the question.

Is this reproducible in a small example that you could post here? Or does it only happen with this database/view/sproc/etc? – Jon Seigel Oct 9 '13 at 1:09
@JonSeigel - I'll see if I can reproduce a small example when I get a chance. I've only ever seen this twice in over 5 years, and both times someone removed the index from a view (different views), so there may be other factors helping to cause it. – Paul Kroon Oct 9 '13 at 11:50
This would be a bug. I imagine it has been fixed now (2005 SP2 is quite old). – Paul White Aug 7 '14 at 1:25