Don't report error when one Maintenence Plan Task fails

Question

Don't report error when one Maintenence Plan Task fails

Tony

SSC Journeyman

Points: 88
More actions
February 15, 2024 at 10:01 pm

#4379372

I have a Job running a Maintenence Plan. The plan performs the Backup Database, Check database Integrity, Maintenence Cleanup and Reorganise Index tasks. Occasionally the Reorganise Index task fails with the error '"Transaction (Process ID 105) was deadlocked on lock resources with another process and has been chosen as the deadlock victim....'. It runs in the early houtrs when there is little activity. The next day or days it runs ok. I am not concerned about this failing sometimes. I am looking for a way to not report the error on the Reorganise Index task, but continue to report an error if any of the other tasks fail. Is this doable? I notice on the SQL Server Agent Jobs there is a option for each step 'On Failure Action' which can be set to 'Go To the next Step'. Does such a facility exist with Maintenence Plan tasks?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Ed B Hall of Fame Points: 3666 More actions · Answer 1

I imagine the suggestion will be to simply get rid of the index reorg task, however, I think the maintenance plan is essentially an SSIS package. You may be able to set the FailPackageOnFailure property to False for the reorg task. I've never used a maintenance plan created through the wizard, so I don't know if they behave like normal ssis packages.

Tony SSC Journeyman Points: 88 More actions · Answer 2

Thanks Ed, The 'FailPackageOnFailure' is by default False and what it is set to.

Tony SSC Journeyman Points: 88 More actions · Answer 3

I have decided to split the job and move the Reorganise index to another maintenence Plan. In the jobs I have also created another job to run the reorganise maintenence Plan. Setting the 'on failure action' to 'Quit the job reporting success'. I could have used the same job but decided on a seperate job and perform the reorganise only once a week.

This reply was modified 9 months, 1 week ago by Tony.
This reply was modified 9 months, 1 week ago by Tony.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Tony wrote:

I have a Job running a Maintenence Plan. The plan performs the Backup Database, Check database Integrity, Maintenence Cleanup and Reorganise Index tasks. Occasionally the Reorganise Index task fails with the error '"Transaction (Process ID 105) was deadlocked on lock resources with another process and has been chosen as the deadlock victim....'. It runs in the early houtrs when there is little activity. The next day or days it runs ok. I am not concerned about this failing sometimes. I am looking for a way to not report the error on the Reorganise Index task, but continue to report an error if any of the other tasks fail. Is this doable? I notice on the SQL Server Agent Jobs there is a option for each step 'On Failure Action' which can be set to 'Go To the next Step'. Does such a facility exist with Maintenence Plan tasks?

My recommendation is that you don't actually know what REORGANIZE does nor how it actually works. If you did, you wouldn't use it.

I strongly recommend that it's better to do no index maintenance than it is to do it wrong and, if you're using REORGANIZE, then there's a really good chance that you're doing it wrong.

Save yourself some aggravation... just rebuild stats with full scan and the only time you should be doing index maintenance is if the page density drops to something less than 80% full. Then, only use REBUILD to fix that.

Here's a challenge for you... do all of your index maintenance and then measure the performance by looking at the run durations of all your jobs for the next week. Do not do any index maintenance for a month and then check the jobs again. Unless you have a certain type of indexes, I believe you'll be shocked at how little your index maintenance has actually been meaning for performance. In fact, you may see a sharp improvement in performance after you stop because you'll no longer be suffering the proverbial "morning after" syndrome after you get done doing your index maintenance.

p.s. My test lasted just 2 months less than 4 years (and I won't even mention how much lower my log file usage got because of so many page splits simply going away). Then, I did some very careful rebuilds to recover some disk space.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)