Technical Article

Check SQL Agent Job Status

,

These functions are especially useful when you have to check the status of the agent jobs running across multiple servers (they will all have different GUIDS).

The first function looks up the agent job id from the sysjobs table. The second function looks up the job status from the sysjobactivity table and returns either one of the following 7 results:

0 - Not executing

1 - Current executing

2 - Waiting for thread

3 - Between retries

4 - Faileure during last run

5 - Suspended

6 - (not used)

7 - Performing completion actions

NULL - Job does not exists

So by easily passing in an agent job name, you can get the current executing status on any given server.

CREATE FUNCTION [dbo].[fx_FetchSQLAgentJobID] (
    @JobName char(250)
)
RETURNS uniqueidentifier AS
/*
    -----------------------------------------------------------------------------------------------------------------------------------
    Purpose:    Returns the job_id for the @job_id parameter of the msdb.dbo.sp_start_job procedure
    Created On:    06/13/2011
    Create By:    MyDoggiejessie
    -----------------------------------------------------------------------------------------------------------------------------------
    SELECT dbo.fx_FetchSQLAgentJobID('YourJob')
*/BEGIN

RETURN (
        (SELECT job_id from msdb.dbo.sysjobs WHERE name = @JobName )
)
END

CREATE FUNCTION [dbo].[fx_FetchJobStatus] (
    @JobID uniqueidentifier
)
RETURNS int AS
/*
    -----------------------------------------------------------------------------------------------------------------------------------
    Purpose:    Returns the status of a given SQL Agent Job - this becomes useful when checking the status of a job before another
                can be run
    Created On:    06/13/2011
    Create By:    MyDoggiejessie
    -----------------------------------------------------------------------------------------------------------------------------------
    SELECT dbo.fx_FetchJobStatus(dbo.fx_FetchSQLAgentJobID('YourJob'))
*/BEGIN

RETURN (
    (SELECT TOP 1 ISNULL(last_executed_step_id, 0)
    FROM msdb.dbo.sysjobactivity 
    WHERE job_id = @JobID
    ORDER BY run_requested_date DESC )
)
END

Rate

3.2 (5)

Share

Share

Rate

3.2 (5)