sp_start_job wait for job to finish

Question

Is there a way to determine when a sql agent job as finished once it has been started with sp_start_job?

Answer 1

XP_SQLAGENT_ENUM_JOBS can be used but it undocumented. It's normally used to detect long running jobs.

Of course, there is also sp_help_jobs or simply monitoring the job history tables

Answer 2

I actually had to do this recently, and this is how I'm thinking of implementing it. I'm creating a temporary job through sp_add_job and sp_add_jobstep, and setting the @delete_level to 3 (always delete after run).

I'm not 100% sold on this approach, as you can probably tell from the title of the stored procedure. However, it does work:

CREATE PROCEDURE spWorkaround_checkJobExists

@job_id UNIQUEIDENTIFIER   
, @thisIteration tinyint  
, @maxRecurse tinyint

AS

IF (@thisIteration <= @maxRecurse)
BEGIN
    IF EXISTS(
    select * FROM msdb.dbo.sysjobs where job_id = @job_id
    ) 
    BEGIN
        WAITFOR DELAY '00:00:01'
        DECLARE @nextIteration int
        SET @nextIteration = @thisIteration + 1
        EXEC dbo.spWorkaround_checkJobExists  @job_id, @nextIteration, @maxRecurse
    END
END

Of course, you'll want to put in some code to ensure that there's a max number of times this will recurse, but you get the idea. You could also pass in a parameter to control how often the recursion happens. In my case, after ten seconds, the results are meaningless.

What I'm doing here could be modified for jobs that aren't meant to be dropped immediately after executing by changing the selection criteria to check the execution status of the job, for instance, with sp_help_job passing @job_name or @job_id and @execution_status = 0.

Any comments are encouraged.

HTH!

Cheers,

-m