DB Maintenance Plan error....

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe - 28 Dec 2007 09:56 GMT

I have a Maintenance Plan that keeps failing on an inherited system. The
error is:

[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 used to run fine, but recently stopped working. There are two indexes
the PK_index and IX_index but they are on the same columns. Isn't that
redundant.

I'm not sure what the next step is. I would appreciate any assistance.

Reply to this Message

Joe - 28 Dec 2007 10:05 GMT

Oh, this is a SQL 2000 SP3 running on Windows 2000. Upgrading to SP4 isn't
planned as of yet.

> I have a Maintenance Plan that keeps failing on an inherited system. The
> error is:
[quoted text clipped - 8 lines]
>
> I'm not sure what the next step is. I would appreciate any assistance.

Reply to this Message

John Bell - 28 Dec 2007 11:57 GMT

Hi Joe

This error usually occurs because there is an index or statistics on a
computed column see http://support.microsoft.com/kb/902388 and
http://support.microsoft.com/kb/902388 if this is the case and you don't have
the SupportComputedColumn option on xp_sqlmaint then you could drop the
index/statistics yourself before running the maintenance task and then
re-create it.

sp_helpindex and sp_helpstatistics will display indexes and statistics on
the table

John

> Oh, this is a SQL 2000 SP3 running on Windows 2000. Upgrading to SP4 isn't
> planned as of yet.
[quoted text clipped - 11 lines]
> >
> > I'm not sure what the next step is. I would appreciate any assistance.

Reply to this Message

Joe - 31 Dec 2007 14:04 GMT

John,

Thanks for the reply. Both links are to the same KB.

The one KB that you reference is for SQL 2000 SP4 only, SP3 doesn't support
the
SupportComputedColumn option.

The indexes are as follows:
IX_index nonclustered, unique located on PRIMARY date, PID
PK_index clustered, unique, primary key located on PRIMARY date, PID

I had tried adding the lines:
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
GO

Prior to kicking off the maintplan but it had no effect. Neither of the
indexed
fields are computed. I'll have to take a closer look at the statistics.

> Hi Joe
>
[quoted text clipped - 25 lines]
> > >
> > > I'm not sure what the next step is. I would appreciate any assistance.

Reply to this Message

John Bell - 31 Dec 2007 15:44 GMT

Hi

Sorry about posting the same link twice, I am not sure what I was supposed
to be pasting there possibly http://support.microsoft.com/kb/301292/

You can certainly drop IX_index.
Setting:

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON

will have no effect as the process that runs SQLMaint is a separate
connection.

Are there any computed columns in the table, not just the indexed ones?

John

> John,
>
[quoted text clipped - 46 lines]
> > > >
> > > > I'm not sure what the next step is. I would appreciate any assistance.

Reply to this Message

Joe - 31 Dec 2007 16:32 GMT

John,

There is one computed column. It is a Year computed from Start Date.
That column does a have a statistic related to it. I will have to go back
to the
developers to find out if that statistic is needed so I can see if I can
remove
it before the reindexing starts.

Thanks again for all your assistance.

> Hi
>
[quoted text clipped - 64 lines]
> > > > >
> > > > > I'm not sure what the next step is. I would appreciate any assistance.

Reply to this Message