Binaryworld - Check job status using T-SQL ... [ SQL Server -> Server Operations ]

You can use the following stored proc to retrive status of SQL Server job.

If user executing this SP is not in sysadmin then make sure that user has "Select" permission on msdb..sysjobs table and execute permission on master.dbo.xp_sqlagent_enum_jobs extended stored proc.

This SP will return the following job status code.

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

Click here to copy the following block

/*
Copyright © 2005 Nayan Patel. All rights reserved.

Author : Nayan S. Patel
Created On : 11/29/2005
Website : www.binaryworld.net

Description: This SP will return job execution status

Note: If user executing this SP is not in sysadmin role then you must grant the following permissions to the user who wants to use this SP

Select Permission on Tables : msdb..sysjobs
Execute Permission on SP : master.dbo.xp_sqlagent_enum_jobs

How to use this SP
==================

--//Example 1
exec usp_CheckJobStatus 'Sync_ConnectWise'

Sample Output:
Sync_ConnectWise Status : 4 [Idle]

--//Example 2
declare @retStatus int
exec @retStatus= usp_CheckJobStatus 'Sync_ConnectWise'
Print @retStatus

Sample Output:
Sync_ConnectWise Status : 4 [Idle]
4

--//Example 3
declare @retStatus int
exec usp_CheckJobStatus 'Sync_ConnectWise',@retStatus OUT
Print @retStatus

Sample Output:
Sync_ConnectWise Status : 4 [Idle]
4

--//Example 4
declare @retStatus int
declare @retStatusDesc varchar(100)
exec usp_CheckJobStatus 'Sync_ConnectWise',@retStatus OUT,@retStatusDesc OUT
Print @retStatus
Print @retStatusDesc

Sample Output:
Sync_ConnectWise Status : 4 [Idle]
4
Idle

*/
Create PROCEDURE usp_CheckJobStatus
(
  @JobName varchar(64),
  @Status int =NULL OUTPUT ,
@StatusDesc varchar(100)=NULL OUTPUT
)
AS

SET NOCOUNT ON

DECLARE @job_id       UNIQUEIDENTIFIER
DECLARE @is_sysadmin INT
DECLARE @job_owner sysname

SET @job_id = (SELECT job_id FROM msdb..sysjobs WHERE name = @JobName)

IF @job_id IS NULL
BEGIN
RAISERROR('The job name specified does not exist',16,1)
return
END

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, -- BOOL
              request_source    INT       NOT NULL,
              request_source_id   sysname     NULL,
              running        INT       NOT
NULL, -- BOOL
              current_step     INT       NOT NULL,
              current_retry_attempt INT       NOT NULL,
              job_state       INT       NOT NULL)

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

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

SET @Status = (SELECT job_state FROM #xp_results
WHERE job_id = @job_id)
DROP TABLE #xp_results

select @StatusDesc=(case
    when @Status=1 then 'Executing'
    when @Status=2 then 'Waiting for thread'
    when @Status=3 then 'Between retries'
    when @Status=4 then 'Idle'
    when @Status=5 then 'Suspended'
    when @Status=7 then 'Performing completion actions'
    else 'UNKNOWN'
   end)

Print @JobName + ' Status : ' + cast(@Status as varchar(10)) + ' ['+ @StatusDesc +']'

--//Return Job Status Code
Return @Status
GO

How to wait until job execution is completed

Some times we need to wait until job is done in this case you can use WAITFOR DELAY statement to check the job status.

Example

Click here to copy the following block

declare @ret int

--//Run Job
exec msdb.dbo.sp_start_job @job_name='test'

--//Wait for completion
while 1=1
begin
WAITFOR DELAY '00:00:05' --//5 second delay (every 5 second check the job status)
exec @ret=usp_CheckJobStatus 'test'
if @ret not in(1,2,3,7)
break
end

--//Do some thing else

Print '--- After job execution ---'

Sample Output

Job 'test' started successfully.
test Status : 1 [Executing]
test Status : 1 [Executing]
test Status : 4 [Idle]

How to check for failed job

Since we dont have any effective way to know the failed status using above stored proc so we need to use another SP called sp_help_jobhistory.

You can pass various parameters to this SP and get the required history for any job.

Click here to copy the following block

--Show all successful (status=1) execution for job "test" on 11/29/2005
msdb.dbo.sp_help_jobhistory @job_name='test',@run_status=1,@start_run_date=20051129
/*
run_status
=======================
0=Failed
1=Succeeded
2=Retry (step only)
3=Canceled
4=In-progress message
5=Unknown

*/