All we need is an easy explanation of the problem, so here it is.
The replies to my previous question: “How to specify a SQL Server maintenance plan subtask to run unconditionally on success or failure of preceding tasks?” answered me that I should change “Evaluation operation:” in my maintenance plan from “Success” to “Completion”.
Though, I am still in doubt about the term “Completion” and that the proposed solution will completely address my situation.
All tasks are configured over “All user databases” that:
- I have not noticed immediately and added my own user (diagnostics, monitoring, testing) database which, then, took offline;
- I am not permitted to change the maintenance plans without previous authorization from company management
So, the nightly databases backup (penultimate) task in screenshot above failed.
There is no diagnostics or logging except the fact that all tasks failed.
I corrected the situation by detaching the culprit offline database. So, after that, maintenance plan job started to complete successfully (with backup task).
But I could not understand:
if I leave a user database offline and all maintenance plan tasks fail to run due to this. Do the tasks considered coming to “completion”?
Some doubts remain because I made other changes which I am sure could not prevent maintenance pla completion. But who knows…
As I could see by the duration of maintenance plan job in comparison to the successful job runs, the the tasks were not even started because their corresponding previous task did not start and they did not start because probably failed to find “All user databases”.
So, the definition of:
Completion requires only that the precedence executable has completed,
without regard to outcome, in order for the constrained executable to
IMO. does not assure the previous task completion because it could have been simply not being started and run at all
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Completion is a task state that is true if:
a. The task state is Success.
b. The task state is Failure.
A task will never reach any of these states until a TaskStart event has caused the task to start executing. A disabled task, or a task that is never executed, will not reach a state of Completion.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂