A successful SQL Server Agent job can still have followed a path that skipped required work or treated a failed step as success. Inspect the job's step-level history and its success/failure routing before rerunning the import. The final status is the result of configured control flow; it is not independent evidence that the intended business output exists.1, 3
This article investigates one job's outcome. It does not repeat the separate migration problem of two servers executing the same schedule. Its examples are inspection and design guidance only; no job, import or failure-injection test was run.
Begin with the missing outcome
State precisely what should have happened. For an import, that might be a particular batch accepted, a known number of source records accounted for and a destination receipt written. For a report, it might be a complete file with the correct reporting period, not merely the presence of a filename.
Record the job identity, SQL instance, scheduled time, actual run interval and expected business batch. Keep the source batch identifier with the job execution. A green run from a different time or environment is not evidence about the missing batch.
Before any retry, establish whether part of the work committed or reached another system. A failed final notification may coexist with a successful import. Repeating the entire job without reconciliation can duplicate work even if the visible symptom was a false success.
The investigation therefore needs two evidence streams: what Agent believed happened at each step, and what the application or destination actually accepted.
Read the step history, not only the summary
In Management Studio, open the intended SQL Server Agent job's View History and expand the relevant execution. Microsoft documents the history viewer and filtering workflow.2 Use existing access appropriate to the job; restricted visibility should be resolved with the DBA rather than by giving a batch application full administrative rights.
Capture step names, identifiers, times, messages, retry outcomes and the final job result. Record any missing history as a limitation. A retention policy, truncated output or overwritten log can leave gaps; a summary row alone cannot reconstruct everything the programme did.
Preserve the job definition as it existed for the incident if versioned evidence is available. The current routing may have changed since the run. If only today's definition survives, state that it is supporting context rather than proof of the earlier path.
Avoid starting the job or selecting a step to run while inspecting it. Close properties dialogs without saving unless a specific change has been approved. Reading configuration and executing a business batch are different actions.
Trace both routes from every step
For each step, inspect its Advanced properties: action on success, action on failure, retry attempts and retry interval. Microsoft permits several actions, including quitting with success, quitting with failure, proceeding to the next step or jumping to a specified step.1, 3
The important surprise is that a step's failure route can explicitly quit the job with success. A successful step can also route to a final failure. Consequently, neither one step's outcome nor the final summary alone tells you whether every mandatory operation completed.3
Write a small route table:
| Step | Required result | On success | On failure | Retry safety |
|---|---|---|---|---|
| Acquire batch | Exact input identified | Validation | Fail/report | Depends on source claim semantics |
| Validate | Required checks pass | Import | Fail/report | Usually read-only, but confirm |
| Import | Destination accepts batch | Verify receipt | Recovery decision | Requires partial-commit analysis |
| Verify | Receipt and totals match | Complete | Fail/report | Must not duplicate import |
This is a hypothetical design aid, not a prescribed job definition. Substitute the real steps, including cleanup, notifications and conditional branches. Mark which steps are mandatory and which may legitimately be skipped.
Follow each route from the configured starting step to a terminal result. Look for a cleanup or notification branch that ends with success even though an earlier mandatory operation failed. Also check that a jump has not accidentally bypassed validation after a step was inserted or reordered.
Understand the step subsystem's contract
The meaning of success depends partly on the subsystem. For CmdExec, the job step has a configured successful process-exit code; Microsoft documents a default of zero.3 A wrapper that launches a child programme and returns its own success without conveying the child's failure can therefore misrepresent the actual work.
Inspect the real command chain, not merely the executable name in Agent. Determine whether it waits for completion and how it propagates the result. A launch acknowledgment, an asynchronous submission receipt and completion of an import are different events. The article does not assume a particular shell or wrapper implementation.
For Transact-SQL steps, examine how the batch handles errors and reports the required outcome. Code that logs a business failure and then continues through an apparent success path needs an explicit contract with the job. Do not treat a printed message containing the word “failed” as equivalent to a failure signal that the subsystem recognises.
Keep return-code, SQL error and business-validation logic distinct. The exact behaviour of PowerShell, integration packages or external utilities needs the documentation for that subsystem and installed version. This guide deliberately does not generalise CmdExec's configured exit-code rule to every Agent step type.
Make output useful without exposing data
Microsoft documents output-file, table and history options for applicable job steps. Output files and table contents can be overwritten unless append behaviour is selected; history output can be truncated.1 Select a bounded retention design that preserves the incident evidence you need.
Prefer a compact structured result containing batch identity, phase, expected and accepted counts, status and a reference to detailed restricted logs. Do not place credentials, connection strings with secrets or full imported customer rows in job history.
A completion receipt should identify the destination and the specific operation, not simply say “done”. If a downstream system accepts work asynchronously, the job must distinguish acceptance from final completion. Decide which state the job is responsible for establishing and which monitoring process owns the remaining transition.
Correct the control flow and failure signal together
If the source defect is a failure route that quits with success, prepare a scoped routing correction. If the wrapper returns success despite failed work, correct the wrapper's result propagation. If the missing output is not checked at all, add an appropriate business verification step. These are different layers and may require different owners.
Do not change every failure route mechanically. Some steps are deliberately optional, and a documented alternate branch may complete the required outcome. The important criterion is that every route producing final success has established the job's agreed obligations.
Keep retries deliberate. Agent supports a configured number and interval, but those settings do not make the operation idempotent.1, 3 Review what a second attempt sees after a partial first attempt. Use batch identifiers, destination receipts or another application-supported method to distinguish already completed work from work still required.
Never make an import repeat indefinitely merely to hide a warning. A retry budget should end with a visible failure and an owner when the condition persists. That preserves both the operational signal and the opportunity to reconcile partial effects.
Verify every meaningful path in isolation
Design a disposable test with synthetic input and a destination that cannot affect customers. Include successful execution, validation failure, programme failure, missing output, a retry after partial progress and failure of a notification or cleanup step. No such test is claimed here.
For each case, record the actual route, step outcomes, final status and destination result. A required import failure must not end as overall success unless a documented recovery branch independently completed the same obligation. A successful import with a failed notification must be reported according to the agreed contract without being blindly imported again.
Before rollout, preserve the previous job definition and wrapper release. A configuration rollback can restore routing, but it cannot undo records already imported or messages already sent. Reconcile those effects separately and ensure only the authorised execution path owns the batch.
Learning notes
- Job success is a configured control-flow outcome.
- Process exit, step completion and business completion need explicit contracts.
- Retry safety depends on partial effects and identity, not on the number of retry attempts.
Acceptance checklist
- [ ] Step success/failure routes and programme exit-code contracts are mapped.
- [ ] Isolated tests exercise failure, missing output and retry after partial progress.
- [ ] Final status agrees with the required destination outcome.
- [ ] Retries and rollback preserve single ownership of each batch and reconcile external effects.
References
- Set Job Step Success or Failure Flow with SQL Server Agent Jobs | Microsoft Learn. Accessed 2026-11-01.
- View the Job History | Microsoft Learn. Accessed 2026-11-01.
- sp_add_jobstep (Transact-SQL) - SQL Server | Microsoft Learn. Accessed 2026-11-01.