November 9, 2016 at 10:01 pm
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.
November 10, 2016 at 12:20 am
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.
November 10, 2016 at 2:06 am
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)
November 10, 2016 at 2:06 am
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
November 10, 2016 at 4:21 am
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?
November 10, 2016 at 4:32 am
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
November 10, 2016 at 4:35 am
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
November 10, 2016 at 4:42 am
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
November 10, 2016 at 4:56 am
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.
November 10, 2016 at 5:10 am
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.
November 10, 2016 at 5:14 am
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
November 10, 2016 at 5:16 am
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
November 12, 2016 at 10:11 pm
how can I find out the reason as there is no corruption in any of the DB's?
November 13, 2016 at 12:50 am
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply