none
Staying with the SQL Agent during Execution

    Question

  • I need to create a stored procedure that triggers off a particular job in the SQL server 2005 agent, waits for the job to complete and then returns back the control to the calling application with the job execution results. As far as triggering off the SQl server agent is concerned, I can use the sp_start_job procedure. But what is the best way to wait for the job to complete execution (Because the sp_start_job proc returns back the control immediately after starting the job). I could use the sp_help_job proc in a loop and wait for the value of the "current_execution_status" column to change from 1 to 4...but is there a better way of doing this? Appreciate inputs here. - Thanks, Manmeet
    Monday, August 07, 2006 11:51 AM

Answers

  • Hi Manmeet,

    What you suggested is appropriate, however I'd recommend that in the loop in which you check if the job is still running, you include a WAITFOR DELAY statement - this will prevent your loop hogging cpu cycles.

    It's also worthwhile to place a WAITFOR statement after you exec sp_start_job, as the job status may not have been updated to "running" before you enter the loop (to check for the job having completed)...

    Cheers

    Rob

    Tuesday, August 08, 2006 3:26 AM

All replies

  • Hi Manmeet,

    What you suggested is appropriate, however I'd recommend that in the loop in which you check if the job is still running, you include a WAITFOR DELAY statement - this will prevent your loop hogging cpu cycles.

    It's also worthwhile to place a WAITFOR statement after you exec sp_start_job, as the job status may not have been updated to "running" before you enter the loop (to check for the job having completed)...

    Cheers

    Rob

    Tuesday, August 08, 2006 3:26 AM
  • Thanks Rob. Really Appreciate you inputs here! Yeah, I am planning to set the polling frequency (time) in the WAITFOR delay based on the average runtime of the job (over a period of a few monitored runs). Thx again!
    Tuesday, August 08, 2006 6:01 AM
  • Hello

    A system stored proc "sp_start_job_and_wait" could be a good feature for next version of sql server, isn't it ?


    Here some code waiting this feature :

    CREATE PROCEDURE sp_JobRunState
    @JobName sysname = NULL,
    @JobID UNIQUEIDENTIFIER = NULL,
    @RunState int OUTPUT
    AS
    BEGIN
    DECLARE @is_sysadmin INT
    DECLARE @job_owner sysname
    DECLARE @wrkJobID UNIQUEIDENTIFIER
    DECLARE @wrkJobName sysname

    SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
    SELECT @job_owner = SUSER_SNAME()

    IF (@JobName = N'')
    SELECT @JobName = NULL

    IF @JobName IS NULL
    IF @JobID IS NULL
    RETURN (-1) -- Need a job parameter

    IF @JobName IS NULL
    SELECT
    @JobName = [name]
    FROM
    msdb.dbo.sysjobs_view
    WHERE
    job_id = @JobID
    ELSE
    IF @JobID IS NULL
    SELECT
    @JobID = job_ID
    FROM
    msdb.dbo.sysjobs_view
    WHERE
    [name] = @JobName
    ELSE
    BEGIN
    SELECT
    @wrkJobID = job_id,
    @wrkJobName = [name]
    FROM
    msdb.dbo.sysjobs_view
    WHERE
    job_id = @JobID

    IF IsNull(@wrkJobName,'') <> @JobName
    RETURN (-2) -- @JobID does not match @JobName
    END

    IF @JobID IS NULL OR @JobName IS NULL
    RETURN (-3) -- No job by that name/id on file

    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,
    request_source int NOT NULL,
    request_source_id sysname,
    running int NOT NULL, -- Boolean 1 Yes 0 No
    current_step int NOT NULL,
    current_retry_attempt int NOT NULL,
    job_state int NOT NULL
    )

    INSERT INTO #xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @JobID
    SELECT
    @RunState = running
    FROM
    #xp_results
    WHERE
    job_id = @JobID

    DROP TABLE #xp_results
    END
    GO


    CREATE PROCEDURE sp_start_job_and_wait
    @Job sysname
    AS
    BEGIN
        DECLARE @JobState int

        Execute sp_start_job @Job
        WaitFor Delay '00:00:02' -- Allow 2 seconds for job to start

        SET @JobState = 1

        WHILE @JobState <> 0
        BEGIN
        WaitFor Delay '00:00:10' -- Check every 10 seconds
        Execute sp_JobRunState @JobName = @Job,
        @RunState = @JobState
        END
    END
    GO

    Nara20
    Thursday, November 27, 2008 2:57 PM
  • I needed to test if a job was running before executing sp_start_job 'JobName' as I would get a 22022 error if it was already running.  And since it might not be in sysjobhistory table if the first step hadn't completed I needed a function to check sysprocesses for the job.

    I tried using some of the other tips I found on the internet, but would a side-effecting error when using:

      INSERT INTO @xp_results
      EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

    So I created this function which appears to work well (at this writing).

    CREATE FUNCTION dbo.xsf_Job_Status
      (@job_name nvarchar(max))
    RETURNS bit
    AS
    BEGIN
     DECLARE @return bit
     DECLARE @job varbinary
     SELECT @job = convert(varbinary,job_id) from msdb.dbo.sysjobs where name = @job_name
     SET @return = CASE WHEN EXISTS
    (select 1 from msdb.dbo.sysjobs
      where name = @job_name
        and convert(varbinary,job_id) in (
     select cast('' as xml).value('xs:hexBinary( substring(sql:column("t.job"), sql:column("t.pos")) )', 'varbinary(max)')
       from (select substring([program_name],30,34) job, case substring(substring([program_name],30,34), 1, 2) when '0x' then 3 else 0 end
              from sys.sysprocesses where CHARINDEX('SQLAgent - TSQL JobStep (Job ',[program_name]) = 1 ) as t(job,pos)))
     THEN 1 ELSE 0 END
    RETURN (@return)
    END
    GO

    Friday, August 27, 2010 8:25 PM