SQL Agent Job - to execute as queue

Question

I have a job which is calling 10 other jobs using sp_start_job. The job is having 10 steps, each step calling each sub jobs, When i execute the main job, i can see it started with step 1 and in a few secods it shows 'finished successfully' But the jobs take long time time, and when i see the log mechanism i have put inside , it shows the all the 10 steps are running simultaniously at the back, till it finishes after few hours. My requirement is, it should finish step 1 first and then only step2 should start. aNY HELP PLS ?

Answer 1

The Microsoft Code forum has a way to check if a stored procedure is running. You could use that to wait until the job is complete:

while 1=1
    begin
    WAITFOR DELAY '000:00:10'

    if not exists (
        SELECT *
        FROM master..sysprocesses p
        JOIN msdb..sysjobs j ON 
            substring(left(j.job_id,8),7,2) + 
            substring(left(j.job_id,8),5,2) +
            substring(left(j.job_id,8),3,2) + 
            substring(left(j.job_id,8),1,2) =
            substring(p.program_name,32,8)
        WHERE j.name = 'YourJobName'
        AND program_name like 'SQLAgent - TSQL JobStep (Job %'
    )
        break
    end

This way the code works is that it waits for 10 seconds, then checks if the job YourJobName is running. It repeats that until the job is no longer running. You could put this in between the sp_start_job calls.

Having said that, there must be an easier way. Can't you store the code for each of the 10 jobs in a stored procedure? The "master" job could call the 10 stored procedures, instead of starting the 10 jobs.

Answer 2

My first answer was that you could use a loop as above but check the job history tables in msdb to wait for the preceding job to finish:

select  sj.name as job_name
from    msdb.dbo.sysjobhistory sjh
    inner join msdb.dbo.sysjobs_view sj on sj.job_id = sjh.job_id
where   sjh.step_id = 0 --Job outcome
    and sjh.run_status = 4 --In progress

Thanks, Andomar, for questioning this. It turns out that sysjobhistory is only updated once the first step has completed. Only an idiot would imagine that, if one value of run_status is 'In progress', the table must be updated when a step starts! I've searched around and this seems to be a tough issue. Somewhere SQL knows what's going on but it doesn't expose the information very well.

It seems you have to choose between miles of complicated code or using an undocumented stored proc. You can easily find the miles of code answers - there are several - by Googling for sysjobhistory. Personally I prefer the xp approach:

create table #xp_results(
    job_id uniqueidentifier not null,
    last_run_date int not null,
    last_run_time int not null,
    next_run_date int not null,
    next_run_time int not null,
    next_run_schedule_id int not null,
    requested_to_run int not null, -- bool
    request_source int not null,
    request_source_id sysname collate database_default null,
    running int not null, -- bool
    current_step int not null,
    current_retry_attempt int not null,
    job_state int not null )

insert #xp_results exec master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = ''

select  sj.name
from    #xp_results xpr
    inner join msdb.dbo.sysjobs_view sj on sj.job_id = xpr.job_id
where running = 1

drop table #xp_results

I've tested this and it really does seem to work. Perhaps it's risky using this xp but that's what the Job Activity Monitor uses - I ran it with the Profiler on - so if it changes they'll probably provide some other way to find this info. As long as you wrap this code up in a function or proc and document that you have a dependency on it, it seems like the least of many evils to me.