Running Status of SQL Agent Jobs

msdb.dbo.sp_help_job would be a simple answer.
But if we want to use the result of sp_help_Job in a programmatic way , like storing the result in a table to do some further processing, it would not be easy.
If you try to execute INSERT into <TableName> EXEC msdb.dbo.sp_help_job command syntax to get the output of this SP into a user table. If you try to use this method, you will find that you get the following error:

Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67

An INSERT EXEC statement cannot be nested.

Well, next thing that any one would do is to see the definition of sp_help_job and quickly figure out that it uses undocumented extended stored procedure (XP) "xp_sqlagent_enum_jobs"

xp_sqlagent_enum_jobs

This XP gives a record set with the following columns

Col Name	Data type
Job_ID	uniqueidentifier
Last_Run_Date	Int
Last_Run_Time	Int
Next_Run_Date	Int
Next_Run_Time	Int
Next_Run_Schedule_ID	Int
Requested_To_Run	Int
Request_Source	Int
Request_Source_ID	varchar(100)
Running	Int
Current_Step	Int
Current_Retry_Attempt	Int
State	Int

The column of interest for us is "State" ofcourse .

Then , what are all the states a SQL agent job can be in ?

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

From the description, its easy to figure out that states (1,2,3,7) are something related to the execution of the Job.

For example,If I want to run a job in while loop code could be as follows:

WHILE(1=1)
BEGIN
DECLARE @JobList TABLE
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)

DECLARE @job_owner sysname
DECLARE @is_sysadmin INT

SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0) SET @job_owner = suser_sname ()
INSERT INTO @JobList   EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

IF NOT EXISTS (SELECT 1 FROM @JobList x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
where x.job_state IN ( 1,2,3,7) -- all the executing Statuses
AND j.name = 'JustWaitFor1-MinJob'
)

EXEC MSDB.DBO.SP_START_JOB N'JustWaitFor1-MinJob'   

DELETE FROM @JobList -- Dont Ask me why. Table variables do not get re-initialized in a while loop

END

But this would fail saying:

Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job JustWaitFor1-MinJob (from User XYZ) refused because the job already has a pending request from User 

The reason is the SQL Agent does not start the Job instantly after you issue the command SP_START_JOB.

To get over this problem we need to use the following in the where condition instead

where (x.running = 1 OR x.requested_to_run = 1)
As the name of the columns suggest 'running' will be 1 if the job is in any state of execution (1,2,3,7) and 'requested_to_run' will be 1 if a request is made and the job has not actually started.