Post reply

Error in Maintenanceplan

  • May 30, 2007 at 2:35 am

    #196048

    Hi,

    We have a re-index process for our productionserver created with Maintenanceplan. Since last weekend the Optimize job ends with an error:

    [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'

    I cannot find this SET statement and wonder, where I could find this, or what causes the problem. Since several databases produce this error, and some of those databases are not changed prior to the run, I wonder what has been changed to cause this nuisance.

    We run SQL Server 2000 with sp4 on Windows 2003 with the latest servivepacks.

    Greetz,
    Hans Brouwer

  • May 30, 2007 at 3:18 am

    #709614

    Just change those settings and try executing the job to see if it succeeds.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • May 30, 2007 at 3:47 am

    #709619

    But those settings are not in the job?!

    This is the jobsteP:

    EXECUTE master.dbo.xp_sqlmaint N'-PlanID 5FD22E54-F42A-49B4-BD91-BE0977CD22D5 -Rpt "d:\database\MSSQL\LOG\Maintenance Main0.txt" -DelTxtRpt 2WEEKS -WriteHistory  -RebldIdx 100 '

    Greetz,
    Hans Brouwer

  • May 30, 2007 at 4:16 am

    #709624

    Hans,

    the problem you have occurs if one of the index columns is a computed column. check this article:

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

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • May 30, 2007 at 5:48 am

    #709636

    Ah yes, I should have known this!!!

    I changed this myself a year ago with similar problem. And now I know some1 changed the maintenanceplans last week...

    Tnx for responding all.

    Greetz,
    Hans Brouwer

  • May 31, 2007 at 9:54 am

    #710056

    Here is a solution provided to me quite some time ago for this 'issue' by MS PSS. It ias not 'elegant' but it works:

    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."

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

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