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.
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
syssessions in order to retrieve the details of the current agent job call.
SELECT j.name AS 'Job Name',
j.job_id AS 'Job ID',
j.originating_server AS 'Server',
a.run_requested_date AS 'Execution Date',
DATEDIFF(SECOND, a.run_requested_date, GETDATE()) AS 'Elapsed(sec)',
CASE WHEN a.last_executed_step_id is null
THEN 'Step 1 executing'
ELSE 'Step ' + CONVERT(VARCHAR(25), last_executed_step_id + 1)
+ ' executing'
END AS 'Progress'
FROM msdb.dbo.sysjobs_view j
INNER JOIN msdb.dbo.sysjobactivity a ON j.job_id = a.job_id
INNER JOIN msdb.dbo.syssessions s ON s.session_id = a.session_id
INNER JOIN (SELECT MAX(agent_start_date) AS max_agent_start_date
FROM msdb.dbo.syssessions) s2 ON s.agent_start_date = s2.max_agent_start_date
WHERE stop_execution_date IS NULL
AND run_requested_date IS NOT NULL
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.
DATEDIFF(MINUTES, a.run_requested_date, GETDATE()) AS 'Elapsed(min)',
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.
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.