Post reply

Reorganize Data Maintenance Plan Step fails

  • December 27, 2005 at 12:35 pm

    #166949

    Hi everyone,

    I saw a post similar to this but not a direct answer to the real question.  I have a maintenance plan step to reorganize the data and it consistently fails with an error message of :

    [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'.

    Does anyone know why this error gets thrown?  I kicked of the job and watched what was executing via profiler and it made it a good part of the way through the tables and then just stops and errors out.  I'm going to modify one of my scripts for doing it my way, but we have these maintenance plans setup a several of our customer's sites and I'd like to be able to give a good reason why not to use the maintenance plan (given that most have very limited I.T. resources).

    Thanks in advance for any info.

    Chris

  • December 27, 2005 at 2:16 pm

    #611613

    There is a known issue with indexes on computed columns that has plagued maintenance plans for years now. As of SP4 there is a new option that handles this properly:

    Event ID: 208 may be logged, and a "DBCC failed" error message is logged when you try to use the Database Maintenance Plan Wizard to create a maintenance plan in SQL Server 2000

    That's the most common reason. Does that happen to apply in your case?

    K. Brian Kelley
    @kbriankelley

  • December 27, 2005 at 3:49 pm

    #611627

    Yep that would be the cause.  Thanks for the info.

  • December 28, 2005 at 11:47 am

    #611749

    Here's a script provided by MS PSS as a work around:

    SET ANSI_NULLS ON

    SET ANSI_PADDING ON

    SET ANSI_WARNINGS ON

    SET ARITHABORT ON

    SET CONCAT_NULL_YIELDS_NULL ON

    SET QUOTED_IDENTIFIER ON

    SET NUMERIC_ROUNDABORT OFF

    GO

    EXEC SP_MSForEachTable "DBCC DBREINDEX ('?')"

    GO

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

  • December 28, 2005 at 2:17 pm

    #611794

    Yup. If you can't go to SP4 and use the switch, this is the way to go. If you're on SP4, the switch should be sufficient.

    K. Brian Kelley
    @kbriankelley

  • June 28, 2006 at 9:52 am

    #646410

    Is re-indexing all that the optimization job does?

    Additionally, what if the column that is a calculated/computed column does not have an index?

    Thanks.

    Chris...

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

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