none
Optimization job failed in particular database

    Question

  • Hi All,

    We are getting the below error while running the optimization job for particular database.

     "USE [IDITProd_DB]  ".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.29     Source: Rebuild Index Task      Executing query "ALTER INDEX [AC_A...  The package execution fa...  The step failed.
    Tuesday, December 26, 2017 8:42 PM

All replies

  • Full Error log:

    Executed as user: NT Service\SQLSERVERAGENT. ...00.8 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  10:44:25 PM  Progress: 2017-12-26 22:44:25.78     Source: {DB7DC930-DF95-4413-8368-6ADE5CB12080}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Progress: 2017-12-26 22:44:30.22     Source: Rebuild Index Task      Executing query "USE [IDITProd_DB]  ".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.22     Source: Rebuild Index Task      Executing query "ALTER INDEX [AC_ACCOUNT_INDX] ON [dbo].[AC_ACCOUNT...".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.22     Source: Rebuild Index Task      Executing query "USE [IDITProd_DB]  ".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.24     Source: Rebuild Index Task      Executing query "ALTER INDEX [AC_ACCOUNT_NN1] ON [dbo].[AC_ACCOUNT]...".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.24     Source: Rebuild Index Task      Executing query "USE [IDITProd_DB]  ".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.24     Source: Rebuild Index Task      Executing query "ALTER INDEX [AC_ACCOUNT_NN10] ON [dbo].[AC_ACCOUNT...".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.24     Source: Rebuild Index Task      Executing query "USE [IDITProd_DB]  ".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.24     Source: Rebuild Index Task      Executing query "ALTER INDEX [AC_ACCOUNT_NN2] ON [dbo].[AC_ACCOUNT]...".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.24     Source: Rebuild Index Task      Executing query "USE [IDITProd_DB]  ".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.25     Source: Rebuild Index Task      Executing query "ALTER INDEX [AC_ACCOUNT_NN3] ON [dbo].[AC_ACCOUNT]...".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.25     Source: Rebuild Index Task      Executing query "USE [IDITProd_DB]  ".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.25     Source: Rebuild Index Task      Executing query "ALTER INDEX [AC_ACCOUNT_NN4] ON [dbo].[AC_ACCOUNT]...".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.25     Source: Rebuild Index Task      Executing query "USE [IDITProd_DB]  ".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.25     Source: Rebuild Index Task      Executing query "ALTER INDEX [AC_ACCOUNT_NN5] ON [dbo].[AC_ACCOUNT]...".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.25     Source: Rebuild Index Task      Executing query "USE [IDITProd_DB]  ".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.25     Source: Rebuild Index Task      Executing query "ALTER INDEX [AC_ACCOUNT_NN6] ON [dbo].[AC_ACCOUNT]...".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.25     Source: Rebuild Index Task      Executing query "USE [IDITProd_DB]  ".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.27     Source: Rebuild Index Task      Executing query "ALTER INDEX [AC_ACCOUNT_NN7] ON [dbo].[AC_ACCOUNT]...".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.27     Source: Rebuild Index Task      Executing query "USE [IDITProd_DB]  ".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.27     Source: Rebuild Index Task      Executing query "ALTER INDEX [AC_ACCOUNT_NN8] ON [dbo].[AC_ACCOUNT]...".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.27     Source: Rebuild Index Task      Executing query "USE [IDITProd_DB]  ".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.27     Source: Rebuild Index Task      Executing query "ALTER INDEX [AC_ACCOUNT_NN9] ON [dbo].[AC_ACCOUNT]...".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.27     Source: Rebuild Index Task      Executing query "USE [IDITProd_DB]  ".: 0% complete  End Progress  Progress: 2017-12-26 22:44:30.29     Source: Rebuild Index Task      Executing query "ALTER INDEX [AC_A...  The package execution fa...  The step failed.

    Tuesday, December 26, 2017 8:43 PM
  • Use the Advanced tab in the Step definition. There you can direct output to a file, so you can capture the full output. Why this execution failed, no one knows, unless you can find a matching error in the SQL Server errorlog.

    As the reason may be corruption, I would recommend that you rerun the job once you have changed the output settings.

    Tuesday, December 26, 2017 9:01 PM
  • thanks for your response...

    i given the output file in the location...i'm getting the below error in txt file.

    Error:

    Microsoft (R) SQL Server Execute Package Utility
    Version 12.0.2000.8 for 64-bit
    Copyright (C) Microsoft Corporation. All rights reserved.

    Started:  2:35:35 AM
    Error: 2017-12-27 02:35:57.86
       Code: 0xC002F210
       Source: Rebuild Index Task Execute SQL Task
       Description: Executing the query "ALTER INDEX [T_META_DATA_UNQ] ON [dbo].[T_META_DAT..." failed with the following error: "The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.T_META_DATA' and the index name 'T_META_DATA_UNQ'. The duplicate key value is (automaticalarmFlag, 15848, <NULL>, <NULL>).
    The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
    End Error
    DTExec: The package execution returned DTSER_FAILURE (1).
    Started:  2:35:35 AM
    Finished: 2:35:57 AM
    Elapsed:  22.141 seconds

     
    Tuesday, December 26, 2017 9:09 PM
  • Well, this error is very descriptive. The table t_meta_data somehow managed to get non-unique data, so the index can not be created. You may either delete that bad row (and figure out how it got there) or change the index definition to not be unique.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, December 26, 2017 9:34 PM
    Moderator
  • Thank you very much...for your support
    Tuesday, December 26, 2017 9:41 PM
  • It is worth mentioning that there is one legit situation where this can occur: the index was disabled, and thus the duplicate could be added, and it was detected when the index was re-enabled by the REBUILD. I believe it is not uncommon to disable indexes when loading large amounts of data into a data warehouse.

    But if you know that the index was never disabled, this is smoke that indicates that there is fire somewhere - that is, corruption. In that case, you should run DBCC CHECKDB to check the overall state of the database.

    Tuesday, December 26, 2017 10:02 PM
  • Hi Thanks..

    I have checked the database using DBCC CHECKDB, I'm getting the below output there is no error,so the database is fine right..?..what should i do next..? and i have shared that duplicate key value error to my application team. 

    'CHECKDB found 0 allocation errors and 0 consistency errors in database'

    Thanks & Regards,
    PradeepAnand S
    DBA

    Tuesday, December 26, 2017 10:57 PM
  • Since I don't know anything about your organisation, I can't say whether all is fine or not. But a clean DBCC is always good. If the application team says "yeah, we disable that index when we load the warehouse", all is good. But if deny doing any such activities... I would keep my eyes open.

    Tuesday, December 26, 2017 11:13 PM
  • okay..Thanks...Still I'm waiting from application team, i will coordinate with app team...let you know if i need any help from your end..

    Thanks you very much for your quick response.

     
    Tuesday, December 26, 2017 11:29 PM
  • Hi Pradeepanand_DBA,

    Have you checked it from the application team. We are looking forward to your response.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 29, 2017 6:53 AM
  • Hi Teige,

    Sorry for the delayed massage, The application team they are removed the t_mata table and now the job is running fine without issue.

    Thanks,

    PradeepAnand


    Thanks & Regards, PradeepAnand S SQL Database Administrator

    Friday, May 18, 2018 8:24 AM