SQL – How to get the status of an SQL job (sp_help_job)

 

Here is a simple script using sp_help_job to display information about jobs that are used by SQL Server Agent:

USE msdb
EXEC dbo.sp_help_job @JOB_NAME = ‘JOB_NAME’, @job_aspect=‘JOB’

Look for ‘current_execution_status’ to get the status of the job.

1 = Executing.
2 = Waiting for thread.
3 = Between retries.
4 = Idle.
5 = Suspended.
7 = Performing completion actions.

 

Here is a more detailed script to store the results of sp_help_job into a table and query the results:

DECLARE @JOBNAME VARCHAR(100)
SET @JOBNAME = ‘JOB_NAME’

CREATE TABLE #JOBSTATUS(
       job_id uniqueidentifier 
      ,originating_server nvarchar(30)
      ,name sysname
      ,enabled tinyint
      ,description nvarchar(512)
     
,start_step_id int
     
,category sysname
     
,owner sysname
      ,notify_level_eventlog int
     
,notify_level_email int
     
,notify_level_netsend int
     
,notify_level_page int
     
,notify_email_operator sysname
     
,notify_netsend_operator sysname
     
,notify_page_operator sysname
     
,delete_level int
     
,date_created datetime
     
,date_modified datetime
     
,version_number int
     
,last_run_date int
     
,last_run_time int
     
,last_run_outcome int
     
,next_run_date int
     
,next_run_time int
     
,next_run_schedule_id int
     
,current_execution_status int
     
,current_execution_step sysname 
     
,current_retry_attempt int
     
,has_step int
     
,has_schedule int
     
,has_target int
     
,type int     
); 

INSERT INTO #JOBSTATUS
EXEC dbo.sp_help_job
 @JOB_NAME = @JOBNAME
,@job_aspect=‘JOB’

SELECT CASE current_execution_status
WHEN ‘0’ THEN ‘Returns only those jobs that are not idle or suspended. ‘        
WHEN ‘1’ THEN ‘Executing.’
        
WHEN ‘2’ THEN ‘Waiting for thread.’
       
WHEN ‘3’ THEN ‘Between retries.’
        
WHEN ‘4’ THEN ‘Idle.’

WHEN ‘5’ THEN ‘Suspended.’

WHEN ‘6’ THEN

WHEN ‘7’ THEN ‘Performing completion actions.’

ELSE ‘UNKNOWN’
END as ‘current_execution_status’
FROM #JOBSTATUS

DROP TABLE #JOBSTATUS

 

OUTPUT:


current_execution_status
——————————————————–
Idle.

Advertisements

4 thoughts on “SQL – How to get the status of an SQL job (sp_help_job)

  1. did you really get this to work ? It fails under SQL2000 and SQL2008 – An INSERT EXEC statement cannot be nested.

    • Hi Mark

      If you get this error try replacing:

      INSERT INTO #JOBSTATUS
      EXEC dbo.sp_help_job
      @JOB_NAME = @JOBNAME
      ,@job_aspect=‘JOB’

      with:

      INSERT INTO #JOBSTATUS
      SELECT * FROM OPENROWSET(
      ‘sqloledb’
      ,’server=localhost;trusted_connection=yes’
      ,’set fmtonly off exec msdb.dbo.sp_help_job @JOB_NAME = ***YOUR JOB NAME***’)

      • Alternatively, replace it with this code making use of the @JOBNAME variable with a try-catch:

        BEGIN TRY
        INSERT INTO #JOBSTATUS
        EXEC dbo.sp_help_job
        @JOB_NAME = @JOBNAME
        ,@job_aspect='JOB'
        END TRY
        BEGIN CATCH
        DECLARE @COMMAND VARCHAR(1000)
        SET @COMMAND = '
        INSERT INTO #JOBSTATUS
        SELECT * FROM OPENROWSET(
        ''sqloledb''
        ,''server=localhost;trusted_connection=yes''
        ,''set fmtonly off exec msdb.dbo.sp_help_job @JOB_NAME = '+@JOBNAME+''')'
        EXEC(@COMMAND)
        END CATCH

  2. Good article. I wrote the following sql procedure to this.
    DECLARE @JobStatus INT
    SET @JobStatus = 0

    EXEC MSDB.dbo.sp_start_job @Job_Name = ‘$(JobName)’
    SELECT @JobStatus = current_execution_status FROM OPENROWSET(‘SQLNCLI’, ‘Server=$(ServerName);Trusted_Connection=yes;’,
    ‘EXEC MSDB.dbo.sp_help_job @job_name = ”$(JobName)”, @job_aspect = ”JOB” ‘)
    PRINT ‘STATUS: Executing..’
    WHILE @JobStatus 7
    BEGIN
    SELECT @JobStatus = current_execution_status FROM OPENROWSET(‘SQLNCLI’, ‘Server=$(ServerName);Trusted_Connection=yes;’,
    ‘EXEC MSDB.dbo.sp_help_job @job_name = ”$(JobName)”, @job_aspect = ”JOB” ‘)
    PRINT ‘STATUS: Executing..’
    END
    WHILE @JobStatus 4
    BEGIN
    SELECT @JobStatus = current_execution_status FROM OPENROWSET(‘SQLNCLI’, ‘Server=$(ServerName);Trusted_Connection=yes;’,
    ‘EXEC MSDB.dbo.sp_help_job @job_name = ”$(JobName)”, @job_aspect = ”JOB” ‘)
    PRINT ‘STATUS: Performing completion actions ..’
    END
    PRINT ‘STATUS: Successfully completed.’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s