Error during index rebuilds

Question

Error during index rebuilds

aureolin

Hall of Fame

Points: 3520
More actions
August 4, 2005 at 4:10 pm

#90302

I'm getting the following error during an index rebuild step:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
This just started showing up "out of nowhere", in a database that has otherwise had no problems at all.
Steve G.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 1

I'm guessing that you're rebuilding the indexes using a maintenance plan, I've seen this sort of thing before when I used to use them.

I've come to despise maintenance plans over the years and prefer to script each thing seperately so that I know when something is going to run.

Just my thoughts but if you do a reindex without the plan then it would probably work.

aureolin Hall of Fame Points: 3520 More actions · Answer 2

Very curious. I'll try just scripting the DBCC command. What's odd is that this is happening halfway through the reindex process. I can see in the maintenence plan report that the reindex starts, runs for a while and then stops at the same place every time. Made me think it was somehow data related?

Steve G.

Michelle Cook SSCrazy Points: 2119 More actions · Answer 3

Steve,

We have had the same error occur on reindex step of a couple of our maintenance plans, and the error is a result of having an index on a computed column. The fix is to script the reindex job for those databases.

HTH

Michelle

aureolin Hall of Fame Points: 3520 More actions · Answer 4

Now, that's an interesting detail. I do have a computed column in that table - but I've always had a computed column in that table. What's interesting is that the timeframe for when the reindex step started failing is co-incident with the time I updated that column definition. Also, neither the computed column nor the column the computation is based on are indexed...

BTW - I'm in the process of scripting the reindexes using a script found on this site. Gotta love this place!! 😉

Steve G.

Michelle Cook SSCrazy Points: 2119 More actions · Answer 5

Steve,

Could the computed column possibly have a _WA* system created statistic on it? If so, I believe this also causes the index rebuild step of the maintenance plan to fail. Also, if you have a indexed view that uses the computed column, this could also cause the error.

Michelle