reindex maintenance plan failed in sql 2008 (An item with the same key has already been added)

  • Executed as user: DAQMSDBS01\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 9:00:00 PM Error: 2016-11-09 21:00:02.24 Code: 0xC0024104 Source: Rebuild Index Task Description: The Execute method on the task returned error code 0x80070057 (An item with the same key has already been added.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:00:00 PM Finished: 9:06:07 PM Elapsed: 366.555 seconds. The package execution failed. The step failed.

    1 of the blog it shows that there may be some windows update which would fix the issue but I am not sure.

    there nothing in the errorlog as well that time.

  • 21.naved (11/9/2016)


    Executed as user: DAQMSDBS01\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 9:00:00 PM Error: 2016-11-09 21:00:02.24 Code: 0xC0024104 Source: Rebuild Index Task Description: The Execute method on the task returned error code 0x80070057 (An item with the same key has already been added.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:00:00 PM Finished: 9:06:07 PM Elapsed: 366.555 seconds. The package execution failed. The step failed.

    1 of the blog it shows that there may be some windows update which would fix the issue but I am not sure.

    there nothing in the errorlog as well that time.

    What version of SQL Server are you on?

    😎

    SELECT @@VERSION;

    Strongly recommend getting rid of the Maintenance Plans and use Ola Hallengren's Maintenance Solutions instead.

  • Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

  • You might have corruption in that DB. When did you last run a successful checkDB?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • we do not run dbcc checkdb but there have been some reindex jobs successful whereas some failed.

    can I run it online during working hours?

  • 21.naved (11/10/2016)


    we do not run dbcc checkdb

    Seriously? Why on earth not? Do you want to find out about corruption only after it's too late to fix?

    DBCC CheckDB ('<database name>') WITH NO_INFOMSGS

    It's online, but don't run it during heavy user activity, it puts a lot of strain onto a DB.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 21.naved (11/10/2016)


    we do not run dbcc checkdb but there have been some reindex jobs successful whereas some failed.

    can I run it online during working hours?

    Yes you can. Run this command and if there are any errors reported then post the full and unedited output on the data corruption forum.

    😎

    DBCC CheckDB ('DATABASE_NAME') WITH NO_INFOMSGS, ALL_ERRORMSGS

  • Eirikur Eiriksson (11/10/2016)


    21.naved (11/10/2016)


    we do not run dbcc checkdb but there have been some reindex jobs successful whereas some failed.

    can I run it online during working hours?

    Yes you can.

    While it can run online during business hours, it's a very bad idea and not recommended.

    CheckDB puts a lot of load onto a server, running it while users are busy is very likely to severely degrade the performance of the system and can, if the DB and the load is high enough, result in checkDB failing due to sparse file limitations

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (11/10/2016)


    Eirikur Eiriksson (11/10/2016)


    21.naved (11/10/2016)


    we do not run dbcc checkdb but there have been some reindex jobs successful whereas some failed.

    can I run it online during working hours?

    Yes you can.

    While it can run online during business hours, it's a very bad idea and not recommended.

    CheckDB puts a lot of load onto a server, running it while users are busy is very likely to severely degrade the performance of the system and can, if the DB and the load is high enough, result in checkDB failing due to sparse file limitations

    You are absolutely right as always, should have included this warning in my post.

    😎

    The fact that this isn't done regularly makes me wonder about other things such as the setup of the server, backups etc. Often find that if one of the essential things are missing or wrong then one finds few other serious issues as well.

  • I ran dbcc checkdb on all the user db's are no error reported so what should be my next plan of action to find root cause for maint plan failure.

  • I'd take this opportunity to switch from maintenance plans for index rebuilds (which are inefficient and error-prone) to Ola's index maintenance solution (http://ola.hallengren.com/), as well as fixing the other identified maintenance issues, like the lack of a regular consistency check.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 21.naved (11/10/2016)


    I ran dbcc checkdb on all the user db's are no error reported so what should be my next plan of action to find root cause for maint plan failure.

    Suggest that your next action should be patching the server, Microsoft SQL Server 2008 R2 (RTM)

    😎

    I do remember that for 2005 there was a hotfix for this problem, cannot remember if the same applies for Microsoft SQL Server 2008 R2

  • how can I find out the reason as there is no corruption in any of the DB's?

  • I'm going to guess it's a bug in the portion of SSIS that executes maintenance plans (since, on second look, it looks like it might be a .net error)

    Patch the server (There's 4 service packs for 2008 R2 iirc, you shouldn't be running RTM by this point), patch the client as well (SSMS) and seriously consider replacing the maint plan with Ola's scripts, as they're far better.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 14 posts - 1 through 13 (of 13 total)

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