Check Status of SQL Server Job

Question

I want to check if a SQL job is currently running. Is the "run_status" column the correct one to check? Is there a simpler way of doing this without having to loop through each column?

 public int CheckAgentJob(string connectionString, string jobName)
        {
            SqlConnection dbConnection = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand();
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "msdb.dbo.sp_help_jobactivity";
            command.Parameters.AddWithValue("@job_name", jobName);
            command.Connection = dbConnection;
            using (dbConnection)
            {
                dbConnection.Open();
                using (command)
                {
                    SqlDataReader reader = command.ExecuteReader();
                    reader.Read();
                    Object[] values = new Object[reader.FieldCount];
                    int fieldCount = reader.GetValues(values);

                    int jobStatus = -1; // inactive
                    for (int i = 0; i < fieldCount; i++)
                    {
                        object item = values[i];
                        string colName = reader.GetName(i);
                        if (colName == "run_status")
                        {
                            if (values[i] != null)
                            {
                                jobStatus = (int)values[i];
                                break;
                            }
                        }
                    }
                    reader.Close();
                    return jobStatus;
                }
            }
        }

Answer 1

This code is what I needed. Taken from MSDN

Thanks @JeroenMostert

SELECT sj.Name, 
    CASE
        WHEN sja.start_execution_date IS NULL THEN 'Not running'
        WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
        WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
    END AS 'RunStatus'
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
WHERE session_id = (
    SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity); 

Answer 2

You can check it through a stored procedure sp_help_job in the msdb database.

So just run:

Use msdb
go
exec dbo.sp_help_job

it will return all the jobs details where you can find one column named current_execution_status. If it's 1 means it's running. You can get more info from the link sp_help_job

You can provide parameter to the stored procedure as well.