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;
                }
            }
        }

[Please don't put tags in question titles](https://stackoverflow.com/help/tagging) — Liam, Jun 02 '17 at 12:06
You could query [`msdb.dbo.sysjobactivity`](https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobactivity-transact-sql) directly. It's documented; the stored procedure is just a nicety. — Jeroen Mostert, Jun 02 '17 at 12:06
Possible duplicate of [How can I determine the status of a job?](https://stackoverflow.com/questions/200195/how-can-i-determine-the-status-of-a-job) — Liam, Jun 02 '17 at 12:06

score 4 · Accepted Answer · answered Jun 02 '17 at 16:37

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);

score 1 · Answer 2 · edited Jun 02 '17 at 16:28

1

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.

edited Jun 02 '17 at 16:28

marc_s

732,580
175
1,330
1,459

answered Jun 02 '17 at 12:41

Prabir

66
8

Check Status of SQL Server Job

2 Answers2

Linked