SQL 2005 Maint. Plan (Wizard) Failure on Computed Column

Question

SQL 2005 Maint. Plan (Wizard) Failure on Computed Column

Leeland

SSCrazy Eights

Points: 8959
More actions
December 28, 2009 at 10:14 am

#156534

Hi All,
I have a maint. plan on a 2005 server that was created with the maint. plan wizard.
Job does an integrity check and a index rebuild.
The job fails on the following error message
Failed:(-1073548784) Executing the query "ALTER INDEX [P_AlarmDetail] ON [dbo].[AlarmDetail] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
" failed with the following error: "ALTER INDEX failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I open up SSMS and run the above command manually the command completes without error (which is something I don't understand)
Also, doing Google searches on the above error give some insight, and basically make it sound as if you need to maintain a custom script as a result.
Is there a method to deal with these types of tables via the maint. plan wizard?
I found something regarding computed columns for a SQL 2000 maint plan where you added a switch on the job step to handle the computed column but nothing for sql 2005
Any help is appreciated.

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

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

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 1

It is an issue with the index rebuild - it was a problem discovered in SQL 2000. This is the first time I have seen it in SQL 2005. The below KB link has a script that will perform your index rebuilds.

http://support.microsoft.com/kb/301292/en-us

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Leeland SSCrazy Eights Points: 8959 More actions · Answer 2

Thank you for the reply. I did see that kb article through my previous searches on my above issue...and I guess I was looking for a definitive answer regarding SQL 2005.

I found a 'fix' kb for the SQL 2000 maint. plan where they reference adding another parameter in the job step to handle the computed column.

http://support.microsoft.com/kb/902388

I have not found anything online referencing such a fix for a 2005 maintenance plan (when created via a wizard)

I was hoping to get a definitive

Yes Lee you have to create your own custom script to handle a database that has a table with computed columns because a maintenance plan created via a wizard won't handle a index rebuild properly

OR

Yes Lee you can in-fact create a maintenance plan on a database that has a computed column, all you have to do is XXX so the index rebuild will work correctly.

I am not apposed to using a custom script in any way shape or form, and I feel like that is what is going to have to happen with this particular situation.

I would however like to find some documentation regarding the maintenance plan wizard and a computed column to say it will or won't work for my own knowledge and also so I can share it with others

In the event that you have to use a custom script or procedure to maintain the indexes on a database that has table(s) with computed columns...

Does anyone have a good reference to a script they use or have used in the past with good results?

Thanks again for the reply!

Lee

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 3

In the following article: http://msdn.microsoft.com/en-us/library/ms188783(SQL.90).aspx under the heading 'Required SET Options for Indexed Views' it clearly shows what values need to be defined when creating an indexed view.

There is also a disclaimer that states that it is recommended that you set the server wide setting to ON for this option as soon as you have created an indexed view.

Since this option is a connection specific option - setting the value to ON will affect new connections to the system, and where it is not explicitly set for that connection will default to the server setting.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Leeland SSCrazy Eights Points: 8959 More actions · Answer 4

Jeffrey Williams-493691 (12/29/2009)
In the following article: http://msdn.microsoft.com/en-us/library/ms188783(SQL.90).aspx under the heading 'Required SET Options for Indexed Views' it clearly shows what values need to be defined when creating an indexed view.
There is also a disclaimer that states that it is recommended that you set the server wide setting to ON for this option as soon as you have created an indexed view.
Since this option is a connection specific option - setting the value to ON will affect new connections to the system, and where it is not explicitly set for that connection will default to the server setting.

Hi Jeff,

Thanks for the link and it helped clear up some things for me on this topic. I am still doing some reading and research but I believe you have set me on the right path now that I have my answer and can come up with a plan.

Thanks again for taking the time to reply.

Lee