Corrupt index after doing CREATE INDEX WITH(ONLINE=ON)
We have witnessed a strange case of index corruption after trying to create an index ONLINE the last time. The problem manifested itself in that we couldn't do an UPDATE to a few data sets after creating the index ONLINE. We could do SELECT, so we have noticed that the non-updatable rows must have been created (inserted) exactly while the index creation was underway (we have seen it because this is a "purchase" table which has a timestamp for each data set).
The error manifested itself trough a strange ERROR message (which point at low level problems) every time we tried to UPDATE those few data sets on the column which got the new index. The error message was:
Msg 407, Level 16, State 1, Line 1
Internal error. The string routine in file e:\sql10katmait\sql\ntdbms\storeng\dfs\startup\dstream.cpp, line 792 failed with HRESULT 0x8007007a.
Msg 8630, Level 16, State 1, Line 1
Internal Query Processor Error: The query processor encountered an unexpected error during execution.
With every UPDATE (+ Error), the server also generate longer error outputs in the ERROR log AND also created (each time) an MDMP.
The only workaround was to DROP the index in question and recreate it (essentially we did that without ONLINE, to ensure a success as we operate in a mission critical environment).
I also reported the problem trough Microsoft Profession Support, who in the end recommended the same workaround but advised me to report the issue here, if I wanted it to be handled as a Bug. For references I also have the support case number (for the MS Developers), where all the Logfiles and Dumps are already uploaded. It is:
(please refer to that incident for all the files).
Upvotes: 2<=-=Dec 17 2009 6:06AM=-=>
Andras, is the index compressed? Row or page?<=-=Dec 17 2009 6:57AM=-=>
No, no compression at all (nowhere in our db).<=-=Jan 11 2010 6:42AM=-=>
What are the odds to get some response on this issue?<=-=Jan 20 2010 1:13PM=-=>
Andras: Sorry for the delay in the response. I have requested developer to comment on this. I assume that you rebuilt the index. I would like to know if (a) you built it ONLINE and if yes, are you still seeing the issue (b) if you built it OFFLINE, I assume the problem will not come as this is the sceanrio that has worked for you.
Well, the correction of the problem was really – as you assumed – by recreating the same indexes with normal OFFLINE. And then, the problems went away.
But even while doing the ONLINE Index creation which was done on the 8 subtables of a prtitioned view, 6 of the 8 tables didn’t exhibit the problem. So I am almost sure, that it must have been caused by the simultaneous UPDATE to the table AND ONLINE INDEX creation (which also touched the same field the UPDATE went to) … and maybe (I am speculating here) some specifics of how internal locks etc. are handled with partitioned views might come into the mix too.<=-=Jan 28 2010 5:24PM=-=>
Thanks. We will look into this issue. Good to know that it is not blocking you for now…
Thanks for taking the time to report this issue. We have confirmed that this is a bug in Online Index Build. We are proposing to fix this in upcoming CU. Until the bug fix is made available, the recommendation is to refrain from using online index build on tables included in Partitioned Views.