How to Track the Status of Scheduled Jobs in SQL Server?

How to Monitor the Status of Scheduled Jobs in SQL Server

When executing stored procedures that schedule jobs, it is crucial to monitor the progress of these jobs to ensure their timely completion and success. This article will guide you through the steps to determine the status of scheduled jobs in SQL Server:

1. List of Scheduled Jobs Yet to Start

   • Use the following query to retrieve a list of scheduled jobs that have yet to start:

   SELECT name, schedule_next_run
   FROM sysjobs_view
   WHERE next_run_date > GETDATE()

   Copy after login

2. List of Running Jobs

   • To determine which jobs are currently running, execute:

   SELECT job.name, job_id, activity.run_requested_date
   FROM msdb.dbo.sysjobs_view job
   JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
   WHERE DATEADD(HOUR, -1, GETDATE()) < activity.run_requested_date AND activity.run_status <> 2

   Copy after login

3. Job Completion Status

   • The following query can be used to identify jobs that have completed successfully or stopped due to errors:

   SELECT
       job.name, 
       job.job_id, 
       job.originating_server, 
       activity.run_requested_date, 
       DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) as Elapsed
   FROM 
       msdb.dbo.sysjobs_view job
   JOIN
       msdb.dbo.sysjobactivity activity
   ON 
       job.job_id = activity.job_id
   JOIN
       msdb.dbo.syssessions sess
   ON
       sess.session_id = activity.session_id
   JOIN
   (
       SELECT
           MAX( agent_start_date ) AS max_agent_start_date
       FROM
           msdb.dbo.syssessions
   ) sess_max
   ON
       sess.agent_start_date = sess_max.max_agent_start_date
   WHERE 
       run_requested_date IS NOT NULL AND stop_execution_date IS NULL

   Copy after login

The above is the detailed content of How to Track the Status of Scheduled Jobs in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!