Weekly Reindex Job Failed - question

Question

Weekly Reindex Job Failed - question

Stephen Rybacki

Hall of Fame

Points: 3402
More actions
May 18, 2015 at 6:43 am

#304108

Hi folks...
I have a weekly Maintenance Plan Reindex job that has failed because of a deadlock. My question seems simple enough and I'm ashamed to say I ought to know this answer, but here goes: Does the rest of a given job continue after such failures (this one was maybe 3/4 through the log) occur?

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

The ALTER INDEX statement that was the deadlock victim will roll back. What happens after that depends on the severity of the error and how your code handles it.

John

Stephen Rybacki Hall of Fame Points: 3402 More actions · Answer 2

John Mitchell-245523 (5/18/2015)
The ALTER INDEX statement that was the deadlock victim will roll back. What happens after that depends on the severity of the error and how your code handles it.
John

I am simply using the basic "Wizard" maintenance plan item for rebuilding all indexes. I rarely have this or any) problem given that, but then too I have made timing adjustments and don't use things like "Shrink Database" task as per recommendations found here and elsewhere. So then, I assume that if that task was rolled back, and I have no code in place to act otherwise then the task ended at the point of failure?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I imagine so, yes, but I'm not sure. You should be able to open up the package and script out what it's going to do.

John

Stephen Rybacki Hall of Fame Points: 3402 More actions · Answer 4

John Mitchell-245523 (5/18/2015)
I imagine so, yes, but I'm not sure. You should be able to open up the package and script out what it's going to do.
John

Thanks for the replies John. Yes, I have done that (scripting the expected results) as well as seeing what the Maintenance Plan Reports generated and the point of failure is logged and there seems to be activity after that, so I am going to assume it was just this one index that was skipped due to deadlock and get on with my life lol. I was just back-checking myself really because I am by no means an expert. I am the proverbial "accidental DBA!

Michael L John One Orange Chip Points: 27042 More actions · Answer 5

One of the shortcomings of using the maintenance plan wizard is that it blindly re indexes everything, regardless if the index is fragmented or not.

Subsequently, maintenance takes longer, deadlocks can become a problem, and simply there is work being done that is not needed.

Take a look at this link:

https://ola.hallengren.com/

There are other maintenance solutions similar to this one. They will provide you far more mileage, and control, that the maintenance plan wizards.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Stephen Rybacki Hall of Fame Points: 3402 More actions · Answer 6

Michael L John (5/18/2015)
One of the shortcomings of using the maintenance plan wizard is that it blindly re indexes everything, regardless if the index is fragmented or not.
Subsequently, maintenance takes longer, deadlocks can become a problem, and simply there is work being done that is not needed.
Take a look at this link:
https://ola.hallengren.com/
There are other maintenance solutions similar to this one. They will provide you far more mileage, and control, that the maintenance plan wizards.

Hey, thanks for this! I will seriously look into that -- job security for me! Nobody else around here would EVER try to figure that kind of stuff out lol....