Testing for Job Execution Status

I know I can view a job's status from Enterprise Manager, but I need to view the status from a program or SQL query. How can I create an SQL batch to programmatically test whether SQL Server Agent is running a job?

Say you have a job on your server called InfiniteLoop and you want to see if the job is running. By using SQL Server Profiler to watch the T-SQL code that Enterprise Manager sends to SQL Server, you can see that Enterprise Manager retrieves job-status information by running the sp_help_job stored procedure:

EXEC msdb..sp_help_job@job_name = 'InfiniteLoop',@job_aspect = N'job'

The result set includes a column called current_execution_status, but you might not know how to interpret the column values because SQL Server Books Online (BOL) doesn't document them. However, you can learn a lot by directly reading stored-procedure code. The parameter-declaration section of sp_help_job, as shown below, tells you how to interpret the value of current_execution_status:

@execution_status INT = NULL,  — 1 = Executing,  — 2 = Waiting For Thread,  — 3 = Between Retries,  — 4 = Idle,  — 5 = Suspended,  — 6 = [obsolete],  — 7 = PerformingCompletion  — Actions

For example, a job that's running will have a value of 1; values 2 and 3 show that the job has started but isn't executing commands. Now, you can pass the result set to your program by using the syntax INSERT INTO MyTable EXEC MyProc, which stores sp_help_job's output in a table.

By using Profiler to trace Enterprise Manager's operations, you can find out how to do many things that BOL doesn't document. And reading code in system stored procedures can teach you a lot about SQL Server and how to get information out of it.

See also, "Determining a Stored Procedure's Execution Status."