Querying All Actively Running Agent Jobs In SQL Server

I recently encountered a scenario where I need to to monitor multiple SQL Server agent jobs running simultaneously on a server. Each of these jobs called a separate SSIS (SQL Server Integration Services) package and each job would take around 15-20 minutes to complete as they were part of a large scale data warehousing project.

As anyone who has worked within SQL Server knows, you can easily monitor the progress of a single job by expanding the Jobs node within a connected server instance and viewing the job history as shown below.

view_job_status_sql_mgmt_studio

However, if you want to monitor multiple agent jobs at the same time, this becomes a bit tedious as you have to close the properties window of one job in order to view the properties of another.  It also requires you to expand the latest job call in order to view each step within the agent job.

Fortunately this information can be obtained by querying several of the dynamic management views available within SQL Server.  The following query pulls the basic job information from the sysjobs_view DMV and joins against sysjobactivity and syssessions in order to retrieve the details of the current agent job call.

On small modification I occasionally make to the above script is to change the line of code which calculates the elapsed time.  In the above example the elapsed time will display in seconds. If you would prefer this to be displayed by total minutes of elapsed time, simply replace the line of code with the following.

The output of the query will display any actively running agent jobs along with the execution date, elapsed time and the current job step being executed.  The example below shows two jobs currently running against my SQLSTD-BI server.

active_running_jobs_query_results

The best option for utilizing this script is to create a stored procedure to allow you to easily call the script on the fly. Though I have only included the most relevant information within this script it can further be modified to include additional fields from the various management views depending on the data you are wanting to see from each agent job.