Fix a bug… discover another…  Hence the birth of this post.

I had a production SQL Agent job that called a sub-job using the sp_start_job system stored procedure (step 1).  The next step (step 2) involved a simple update of a configuration table date column.

My assumption was that when step #1 finished, step #2 would update the date and we’d be done.  The flaw in my thinking is that step #2, which happened to update dates that step #1 was using, would run AFTER step #1 finished.

Although not explicitly stated in BOL, the sp_start_job is asynchronous.  It has no concept of what the job that it is calling is doing.  Nor does it wait for the job to return a status.  It simply kicks off the job and that’s it.

The following will demonstrate the asynchronous nature of sp_start_job.

STEP 1: Create a job that can be called that will take a little time.  For this test case I’m just going to force the job to execute the WAITFOR to pause for a 1 minute delay.  The name of this job is Sub-Job.

STEP 2:  Create another job that will have two steps, Parent Job.  The first step will call the SUB-JOB job; the second step will do something.  We don’t care what the second step is.  Our goal is to see if the second step executes AFTER the first step completes.

Here’s the call to the sub-job in Step 1.

The second step happens to be a call to EXEC sp_helpdb.  The only purpose of this call is to do something simple that completes in a short time period.

When we kick off the job it completes almost immediately.  Certainly less than the 1 minute wait that’s in step 1.

Let’s prove our concept by looking at the job history table in msdb:

SELECT  instance_id,
        step_id,
        step_name,
        message,
        run_date,
        run_time,
        run_duration
FROM    msdb.dbo.sysjobhistory
WHERE    instance_id >= 173
ORDER BY 1 ASC;

And here’s the results:

At instance 173 we see the call to the sub-job.  Note the run_duration = 0.  If the job was synchronous this step should have taken 60 seconds.  At line 174 we note the Step 2 kicking off.

Finally, on 176 we see the sub-job running and taking 1 minute (100).

Had the sp_start_job run synchronously line 173 would have had a duration of 1 minute instead of 0.

CONCLUSION:  The system stored procedure sp_start_job is asynchronous.  If you are using this procedure to kick off other jobs all subsequent steps in the calling job stream should be independent of the subordinate job.

This “feature” could be used to start up several jobs concurrently from a single scheduled agent job.  In most cases, however, the use of sp_start_job from SQL Agent should be used with extreme care.

Happy SQL-ing!