What happens if SQL Server job steps are modified during execution

Question

We use SQL Server 2008 and have a job that typically runs 18+ hours and has many steps. I have searched and can't seem to find an answer regarding what happens when you modify a job during execution. Below is a summary of the changes I made to the job during execution today:

1) I inserted a new step at the 'step 2' position. The job was already at step 15 so presumably this step will not fire until the next time the job runs.

2) I deleted the last step (step #30).

3) I modified step 29 from 'go to the next step' to 'Quit reporting success'.

Since step #15 was in progress when I made the change today, will the changes I made to the final steps take effect when the job gets to that point today or will they only be effective the next time it runs?

Also, will the fact that I inserted a new step in the #2 position affect the step order adversely for the current run?

Answer 1

I checked the job after it finished and have a conclusive answer now. Modifying a job while it's executing DOES affect the current execution of the job. Based on the job history, the way SQL Server 2008 handles jobs is one step at a time based on step number. The steps are not loaded into memory or stored in any other manner other while the job is executing.

In my specific scenario, I added a step at the #2 position. This caused all subsequent step numbers to be incremented by one at the time of the update. The job was running step #15 when I updated it, so step #15 became #16 post-update. As a result the same stored procedure (new step #16) was executed again when step #15 finished.

Also, since I removed the final step of the job, it was not executed when the job reached that step as it no longer existed as part of the job.

The bottom line is that best practice is not to update jobs during execution. The results are predictable, but unless you can afford for steps to run twice or not at all, it's not recommended.