Post reply

Optimization job for Maintenance plan ''db name'' failed

  • December 4, 2005 at 7:22 pm

    #112828

    Hi all,

    I have a database (share point db). I creaeted a maint plan to rebuild indexes. The job fails. Here is the output of the maint plan

    Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'XXX47XX' as 'AXN\SQLuser' (trusted)

    Starting maintenance plan 'XX_SITE_22' on 12/4/2005 1:32:05 AM

    [1] Database XX_SITE_22: Index Rebuild (leaving 10%% free space)...

        Rebuilding indexes for table 'Categories'

        Rebuilding indexes for table 'ComMd'

        Rebuilding indexes for table 'Deps'

        Rebuilding indexes for table 'Docs'

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

        ** Execution Time: 0 hrs, 0 mins, 2 secs **

    Deleting old text reports...    0 file(s) deleted.

    End of maintenance plan 'XX_SITE_22' on 12/4/2005 1:00:45 AM

    SQLMAINT.EXE Process Exit Code: 1 (Failed)

    I tried to check if there are any indexes on computed columns. Can some one give me the script to do check if indexes are present in computed columns

    Any suggestions/ideas?


    Helen
    --------------------------------
    Are you a born again. He is Jehova Jirah unto me

  • December 5, 2005 at 8:23 am

    #607713

    Helen

    Please have a look @ http://support.microsoft.com/?kbid=902388

  • December 6, 2005 at 10:08 am

    #607984

    We had this problem with the MON OnePoint database some time ago and opened up a case with MS. Here's a script provided by PSS for the problem as a workaround:

     

    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 6, 2005 at 5:53 pm

    #608087

    Hi rudy,

    Thanks a lot for your help. It works for me now. One more question - How can I get the output of execution if I run this script? and any idea/script to see whether indexes are present in computed columns?

     


    Helen
    --------------------------------
    Are you a born again. He is Jehova Jirah unto me

  • December 6, 2005 at 7:09 pm

    #608105

    We had the same problem and i determined it was probably due to our indexed views or computed columns.  We did the workaround suggested by Rudy, but I do like Gagandeep's answer because it at least confirms my suspicions (can tell customer's server people that we are not bad DB people!) and shows that MS has a workaround, however kludgy. 

  • December 7, 2005 at 11:50 am

    #608239

    DBCC DBREINDEX (tablename) only produces the following output:

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

     

    As for capturing the output, I use an output file in the Task Scheduler.

    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