5

I am having a (rather standard) daily maintenance plan created with SQL Server Management Studio Maintenance Plan Wizard.

enter image description here

AFAIU, the penultimate task "Back Up Database Task" will be executed only on the success of all preceding tasks. Since the constraints (arrows containing tasks) are all the same (taken from default options creation):

enter image description here

and use "Logical AND"

Now if I change in the preceding constraint the "Logical AND" to "Logical OR", the backup task still will fail if one of the preceding tasks fail.
Correct?

How can I make the backup task to run in any case?

UPDATE
After accepting the answers, I am still in doubt about the term "completion":
Does failed task considered to be run to completion in SQL Server maintenance plan terminology?

Community
  • 1
Fulproof
  • 1,362
  • 2
  • 25
  • 33

2 Answers2

5

There are 3 options in Value: Success, Failure and Completion (approximate). Changing the value to Completion for all the preceding precedence constraints should ensure that the backup task is reached regardless of the upstream tasks.

Execution Results

The precedence constraint can use the following execution results alone or in combination with an expression.

  • Completion requires only that the precedence executable has completed, without regard to outcome, in order for the constrained executable to run.
  • Success requires that the precedence executable must complete successfully for the constrained executable to run.
  • Failure requires that the precedence executable fail for the constrained executable to run.

Further reading:

billinkc
  • 15,453
  • 4
  • 46
  • 87
  • That was my first thought though I was in some doubt about the "completion". This term sounds ambiguous to me. What if there is a chance that a task do not run to completion (if it fails or is cancelled due a deadlock)? Couldn't find any explanation about it as well as the use of expressions instead oe with constraints – Fulproof Sep 25 '13 at 01:47
  • Popped a few links in there to provide further reading. Hopefully, you can set your mind at ease w.r.t. the "ambiguity". Links should also cover expressions as well – billinkc Sep 25 '13 at 05:14
  • @billinks, thanks, though I already shooted another follow-up question. I upvoted your reply but I have the naughty habit of accepting the less voted question amongst a few correct answers – Fulproof Sep 25 '13 at 07:13
2

You need to change the appropriate upstream precedence constraint(s) in the flow to On Completion instead of On Success.

This is done one of two ways:

  1. Right-clicking the constraint on the design surface (shown below) and selecting the appropriate type from the popup menu.
  2. In the Precedence Constraint Editor dialog (which you had open), select the appropriate type from the Value dropdown list.

In earlier versions than 2012, the constraint arrow will turn blue; in 2012, the arrow gets a more explicit "Completion" caption.

enter image description here

Now having said that, if you want to keep all of this in one flow, I would actually recommend using SQL Server Data Tools to create the package, because what you really need is a Sequence Container to put all the maintenance stuff in (keeping the existing On Success constraint types), and then connect the container to the backups task with an On Completion precedence constraint.

Alternatively -- and this is what I would usually do -- separate the backup portion of the process into a different subplan, and schedule it after the maintenance portion is expected to complete.

Jon Seigel
  • 16,673
  • 6
  • 41
  • 83
  • @JonSiegel, I'd appreciate if you check my UPDATE in body of question – Fulproof Sep 25 '13 at 07:08
  • I am still in doubt that changing of "Success" to "Completion" assures the following task to start because previous task simply was not even tried to start running – Fulproof Sep 25 '13 at 22:07
  • @Fulproof: This is why I said what you really need to use (if you want to keep this in the same flow) is a Sequence Container. It releases the dependency from the preceding *task*, and puts it on the whole *group* of tasks. Or, separate the task into a different subplan and execute it separately. – Jon Seigel Sep 26 '13 at 13:55