sp_start_job wait for job to finish [duplicate]

Question

Possible Duplicate:
Executing SQL Server Agent Job from a stored procedure and returning job result

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

Proprietary

Answer 1

This article (wayback machine version) describes an SP to launch a sql agent job and wait.

-- output from stored procedure xp_sqlagent_enum_jobs is captured in the following table
    declare @xp_results TABLE ( 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)

    -- start the job
    declare @r as int
    exec @r = msdb..sp_start_job @job

    -- quit if unable to start
    if @r<>0
        RAISERROR (N'Could not start job: %s.', 16, 2, @job)

    -- start with an initial delay to allow the job to appear in the job list (maybe I am missing something ?)
    WAITFOR DELAY '0:0:01';
    set @seccount = 1

    -- check job run state
    insert into @xp_results
    execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

    set @running= (SELECT top 1 running from @xp_results)

    while @running<>0
    begin
        WAITFOR DELAY '0:0:01';
        set @seccount = @seccount + 1

        delete from @xp_results

        insert into @xp_results
        execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

        set @running= (SELECT top 1 running from @xp_results)
    end

    -- result: not ok (=1) if still running

    if @running <> 0 begin
        -- still running
        return 0
    end
    else begin

        -- did it finish ok ?
        set @run_status = 0

        select @run_status=run_status
        from msdb.dbo.sysjobhistory
        where job_id=@job_id
          and cast(run_date as bigint) * 1000000 + run_time >= @start_job

        if @run_status=1
            return 1  --finished ok
        else  --error
            RAISERROR (N'job %s did not finish successfully.', 16, 2, @job)

    end

    END TRY

Infernal

that article helped me, anyway... – Infernal

Answer 2

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

Cypher

I'd prefer to stay in documented land. I am aware of sp_help_jobs, however it's kind of ugly, as, from my understanding, I need to read the result set into a temp table then select from that. I was looking for something a little cleaner, although this would work. – Proprietary

Answer 3

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.

Deformed

Answer 4

 sp_help_job   @job_name   @execution_status = 0

Leoni

sp_help_job returns several result sets, so a little bit more code, how you consider to use this in sp_help_job in TSQL to check for a running job would be more helpful than three words of code, that would not even run without correction – Amati

Answer 5

SELECT TOP 1 1 AS FinishedRunning
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NOT NULL
AND aj.start_execution_date IS NOT NULL
AND sj.name = 'YourJobNameHere'
AND NOT EXISTS
(
    SELECT TOP 1 1
    FROM msdb..sysjobactivity New
    WHERE New.job_id = aj.job_id
    AND new.start_execution_date > aj.start_execution_date
)

Carberry