January 25, 2010 at 10:47 am
I have sql server 2005 SP3.
I have these 2 user maintenance plan on my same server.
Please take a look at following attachment called maint-plan-1
When I run this maint plan, It executes checkdb task then rebuild index task and then stops
it wont go to next step and it doesnt even says that there is an error in rebuild index task
----------------------------------------------------------------------
Also Please see attachement called maint-plan-2 in the figure
Here it performs each n every task without any error.
I believe logically the design of maint-plan-1 is good and it should perform all
the tasks but practically i m getting different result.
Can you explain me why the first design is not giving desired outcome?
Thanks
January 25, 2010 at 1:22 pm
From the first look at it i would say the reorganize task is waiting for the completion of the notify operator task and the rebuild which will not happen.
January 25, 2010 at 1:32 pm
Why are you reorganizing the indexes after rebuilding them?, apparently that's what the green arrow between Rebuild and Reorganize does.
Instead take out the green arrow and redirect it to maintenance cleanup task.
Thanks...
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 25, 2010 at 2:17 pm
I will start my comment with the fact that you should change your process to either REINDEX or REORGANIZE - not both.
Now, the reason this plan does not work is because you have set the precedence constraints incorrectly. How can the Reorganize task execute when the contraints are restricting the task from running only when you have both a successfully reindex task AND upon completion of the notify operator task.
Since you can never have both of those true with this setup - the job is stopping at that point.
What I would do is remove the extra notify operator tasks - you only need one of them. Connect each task to that single notify operator task on failure. Then modify the constraint on one of those and change it to a logical OR under the multiple contraints section. That will change the constraint to a dashed red line. Now, if any task fails - you will get a notification that the job failed.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 25, 2010 at 3:51 pm
Hey Jeffery,
I agree with your answer.
AS per you,
What I would do is remove the extra notify operator tasks - you only need one of them. Connect each task to that single notify operator task on failure. Then modify the constraint on one of those and change it to a logical OR under the multiple contraints section. That will change the constraint to a dashed red line. Now, if any task fails - you will get a notification that the job failed.
If we can use only one notify operator task then, how would we know what task is failed? Can we do something abt it?
January 25, 2010 at 7:12 pm
If you get a notification - one of the tasks in that job failed. You then view the history for the maintenance plan (not the agent job) - and that history will show you which tasks in the maintenance plan were successful and which tasks failed.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 5, 2011 at 9:32 am
HEllo Jeff, Thats right but due to some reasons, I need to specify what task failed and get emails.
So I did the changes and things worked fine so far. But I have one silly question poped up
in my mind.I need to know what is correct.
--Please see 2 attached figures.
1) In plan-1 I have a execute sql task which has 1 statement. now if this statement will run fine
then as per plan-1 it will go to notify operator 3 as there is a green dotted line.
So on Notify operator 3, there are 2 signals coming and only one of them should come and not both will
come so both signals are dotted because it should get only one signal and not wait for
both of them. I understand that.
But what I dont understand is, in the same figure, from execute sql task 2 signals are going,
either successful--which is dotted green
OR Fail--which is solid RED in my case. Is that correct?? or it should be dotted REd???
So what I dont understand is, when 2 signals are going from one task and only one signal should go,
either success or fail then those signals should be dotted or solid??
May 5, 2011 at 1:54 pm
Looking at how you have this set up - what I see is this:
On failure, execute Notify Operator Task 1.
On success, execute Notify Operator Task 3.
So far, so good - but I don't understand why you also put a dependency from Notify Operator Task 1 to Notify Operator Task 3. If you just change the dependency from your Execute T-SQL Statement Task from on success to on completion - then that notification will be sent out all the time.
In short, remove the blue dependency between the notify operator tasks and change the on success dependency to on completion.
In this particular case, since you only have a single task there is no reason to worry about change the AND/OR option for the dependency.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply