Checking Agent Job Status Using TSQL

The below post describes how to verify a job status using TSQL. There are two different ways to identify the job status other than SSMS

Below are the two stored procedures.
sp_help_job -- Using this stored procedure current_execution status column we can identify the job status.
In this stored procedure the is a parameter called @job_aspect which returns different set of data based on it is value

@job_aspect Parameter values are - JOB, STEPS, SCHEDULES, TARGETS, ALL

-- To view only Job steps of a given job, below is the syntax

USE msdb ;
GO
EXEC dbo.sp_help_job
@job_name = N'FullBackups - Every Night',
@job_aspect = 'STEPS' ;
GO


xp_sqlagent_enum_jobs (undocumented extended stored procedure).

xp_sqlagent_enum_jobs -- State column describes the state of the job
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions

Syntax to execute
EXEC xp_sqlagent_enum_jobs 1, ''