How to Execute and Monitor an Agent Job Using T-SQL in SQL Server 2005/2008

15
Jan 2010

How to Execute and Monitor an Agent Job Using T-SQL in SQL Server 2005/2008

comment icon16 comment(s) |

In SQL Server 2005 and 2008 it's possible to start an agent job with T-SQL syntax. This is frequently useful. For example, you may have a job that you'd like to have an outside application invoke. Jobs can also be used to execute SSIS packages. Using T-SQL to execute a job that runs an SSIS package is a way to provide an application access to an SSIS package.

One potential hangup with running agent jobs from a stored procedure is that control is returned immediately to the SQL that runs the job. Most of the time, it would be useful to know when the job has finished. Perhaps you're calling an SSIS package from a web page and you'd like to update the UI when the package has been completed.

I'll show you how to determine the status of a job that has been invoked, and return upon completion.

First, some background.

Running An Agent Job With T-SQL

The syntax to execute an agent job is as follows:

EXEC MSDB.dbo.sp_start_job @Job_Name = 'JobName'

This will execute the job specified in the @Job_Name parameter of the sp_start_job function. This works as long as the account running the command has permissions to run agent jobs. If you run this, you'll notice that the command returns immediately, proceeding to the next line in the batch. This is no good for most uses.

One way of waiting to return until the job has finished is to drop into a tight loop until the job has completed.

Checking The Status Of An Agent Job In T-SQL

Our plan requires us to be able to check the status of an agent job with T-SQL syntax. We can accomplish this with the following syntax:

EXEC MSDB.dbo.sp_help_job @job_name = 'JobName', @job_aspect = 'JOB'

This will return a record set containing information about our job. The most interesting piece of information for us is the current execution status. When the job is idle sp_help_job will return a value of 4 for the current execution status. If you're interested in what the other values mean, you can dig through the definition of MSDB.dbo.sp_help_job, which will in turn send you digging through MSDB.dbo.sp_get_composite_job_info.

The Wait Loop

Now that we know how to invoke our agent job, and check its status we can start putting it all together.

Our procedure will be something like this:

Call Agent Job
While Execution Status <> 4
(
Poll Execution Status
)

Or, in actual T-SQL:

DECLARE @JobStatus INT
SET @JobStatus = 0
EXEC MSDB.dbo.sp_start_job @Job_Name = 'JobName'
SELECT @JobStatus = current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC MSDB.dbo.sp_help_job @job_name = ''JobName'', @job_aspect = ''JOB'' ')
WHILE @JobStatus <> 4
BEGIN
SELECT @JobStatus = current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',
'EXEC MSDB.dbo.sp_help_job @job_name = ''JobName'', @job_aspect = ''JOB'' ')
END

Getting the job status from the sp_help_job stored procedure requires us to encapsulate it in an OPENROWSET to provide a rowset we can select from. There are other ways to do this, if you're averse to using OPENROWSET. I'll leave them up to you.

The Final Version

Running the query above, you'll notice that it seems to bounce out of the WHILE loop way too quickly. In fact the WHILE loop doesn't even make one iteration. When a job is invoked by sp_start_job the current_execution_status in sp_help_job is not updated instantaneous. It's updated very quickly, but it's possible for your initial query setting @JobStatus to get the ‘4' (Idle) value immediately after you've kicked the job off. The only way I've seen to get around this is to add a WAITFOR command with the DELAY argument in between sp_start_job and the initial sp_help_job check. We can also add a WAITFOR DELAY in the wait loop to prevent the loop from becoming too inefficient. You can tune the WAITFOR value depending on what your job is doing.

DECLARE @JobStatus INT
SET @JobStatus = 0     
EXEC MSDB.dbo.sp_start_job @Job_Name = 'JobName'
WAITFOR DELAY '00:00:01'
SELECT @JobStatus = current_execution_status  FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',  
'EXEC MSDB.dbo.sp_help_job @job_name = ''JobName'', @job_aspect = ''JOB'' ')
 WHILE @JobStatus <> 4
BEGIN
	WAITFOR DELAY '00:00:03'
	SELECT @JobStatus = current_execution_status  FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',  
'EXEC MSDB.dbo.sp_help_job @job_name = ''JobName'', @job_aspect = ''JOB'' ')
END

There are still more improvements that could be made. Perhaps you would like to determine what the other current_execution_status codes are and do something when one of them is detected. One suggestion I'd make is to add a timeout value to the WHILE loop to prevent it from waiting indefinitely if something unexpected happens.
I'll leave that exercise to the reader.

 

Comments

October 11, 2010

Anonymous

How can we use variables for

How can we use variables for the job name here? I thought it would be safer to acquire the job name from database. And writing hard coded statements in a working environment is not always the preferred, at least where I work.
Could you please help, thanks in advance, iris.

January 19, 2011

Anonymous

I have the same question as the above comment

Since I have over 60 jobs, I'd like to run them one by one. How to pass the job name as a parameter? Thanks.

May 20, 2011

RobertG

I really look forward to

I really look forward to reading the blogs here as there is something useful to learn from it like this one which guides us how to execute and monitor an agent Job using T-SQL in the SQL Server 2005/2008 with all the background and other details that we need to understand before we actually get down to doing the “job”! With all the codes and details given, this is really going to be a very helpful to determine the status of a job that has been invoked and come back upon completion!! Is this similar to the payroll software's that we use?

June 10, 2011

David

The OPENROWSET may be bit

The OPENROWSET may be bit down at a SQL installation.

The following script will work with that in mind.

(Take out the PRINT statements, used for display purposes. as you see fit.)

DECLARE @cnt            int,
        @job_id         uniqueidentifier,
        @max            int,
        @name           nvarchar(128),
        @session_id     varchar(32),
        @start_date     varchar(32),
        @stop_date      varchar(32)

SET     @name   = 'MyPlanName'  -- the name of my job
SET     @max    = 360           -- if we poll more than an hour (10-second polls), something is wrong
                                -- (set this to what you might reasonably expect,
                                --  or take out altogether, if preferred)
SELECT  @job_id = job_id
FROM    msdb.dbo.sysjobs
WHERE   name    = @name

IF @name IS NOT NULL
BEGIN
    PRINT   '@name   = ' + @name
    PRINT   '@job_id = ' + convert(varchar(255), @job_id)

    EXEC    msdb.dbo.sp_start_job
            @job_id = @job_id

    SET   @cnt = 0

    WHILE @cnt < @max
    BEGIN
        SET @cnt = @cnt + 1

        SELECT  @session_id = CONVERT(varchar(32), session_id),
                @start_date = CONVERT(varchar(32), start_execution_date, 120),
                @stop_date  = CONVERT(varchar(32), stop_execution_date, 120)
        FROM    msdb.dbo.sysjobactivity
        WHERE   job_id = @job_id

        IF @stop_date IS NOT NULL
            BREAK

        PRINT   CONVERT(varchar, GetDate(), 120) + ' ' + @name + ' running   (' +
                'session=' + ISNULL(@session_id, '') + ' ' +
                'start=' + ISNULL(@start_date, '') + ' ' + 
                'end=' + ISNULL(@stop_date, '') + ')'

        WAITFOR DELAY '00:00:10'
    END --  WHILE @cnt < 100

    PRINT   CONVERT(varchar, GetDate(), 120) + ' ' + @name + ' completed (' +
            'session=' + ISNULL(@session_id, '') + ' ' +
            'start=' + ISNULL(@start_date, '') + ' ' + 
            'end=' + ISNULL(@stop_date, '') + ')'
END -- IF @name IS NOT NULL
June 10, 2011

David

Correction on line 18 WHERE

Correction on line 18

WHERE @name IS NOT NULL

should be:

WHERE @job_id IS NOT NULL
June 23, 2011

Personal Quotes

Personal Quotation

Nice post written by the members

September 14, 2011

Karen C.

Just what I needed....

Thanks so much. I used this as a step in my Log Shipping failover to warm stand-by automation job. I was trying to find out how to find the status of a logshipping backup job and here it was.

April 24, 2012

Kelly

Thx

Very useful. Exactly what I was looking for.

June 22, 2011

Anonymous

Thanks

Very helpful!

November 30, 2011

SQL Lion

ob Running Status report in SSRS

One of the effective and correct way to get the job Running Status report in SSRS for easy viewing and monitoring jobs running on servers. (.rdl file attached.)
http://www.sqllion.com/2011/11/job-running-status-report-in-ssrs/

February 23, 2012

Dinopap

Getting Job result

Very smart solution.

Thanks a lot!

March 23, 2012

Anonymous

sweet! dude!

sweet! dude!

March 23, 2012

Anonymous

Just a huge thanks - that's

Just a huge thanks - that's exactly what I needed!

September 27, 2012

Anonymous

i have set up a job to run

i have set up a job to run every five minutes.
If somehow the job takes more than five minutes in execution, will the sql scheduler run another instance of same job on five minutes cycle? when the last execution has not been completed?

September 23, 2013

Neoafter

No there will only be 1

No there will only be 1 instance of the job running at any one time.

January 22, 2014

Anonymous

no, this is five minutes

no, this is five minutes after job finished, for example your job is not going to execut as it:
12.00, 12:05,12:10,12:15

its going to execute as it
12:00 (job takes 30 seconds) then next execute five minutes after this job finished next will be execute to 12:05:30 (not this takes 2 minutes)
next is going to execute to 12:12:30...

Search