How can I determine the status of a job?

Question

I have a Stored procedure which schedules a job. This Job takes a lot of time to get completed (approx 30 to 40 min). I need to get to know the status of this Job. Below details would help me

1) How to see the list of all jobs that have got scheduled for a future time and are yet to start

2) How to see the the list of jobs running and the time span from when they are running

3) How to see if the job has completed successfully or has stoped in between because of any error.

Answer 1

I would like to point out that none of the T-SQL on this page will work precisely because none of them join to the syssessions table to get only the current session and therefore could include false positives.

See this for reference: What does it mean to have jobs with a null stop date?

You can also validate this by analyzing the sp_help_jobactivity procedure in msdb.

I realize that this is an old message on SO, but I found this message only partially helpful because of the problem.

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 

Answer 2

You could try using the system stored procedure sp_help_job. This returns information on the job, its steps, schedules and servers. For example

EXEC msdb.dbo.sp_help_job @Job_name = 'Your Job Name' 

SQL Books Online should contain lots of information about the records it returns.

For returning information on multiple jobs, you could try querying the following system tables which hold the various bits of information on the job

• msdb.dbo.SysJobs
• msdb.dbo.SysJobSteps
• msdb.dbo.SysJobSchedules
• msdb.dbo.SysJobServers
• msdb.dbo.SysJobHistory

Their names are fairly self-explanatory (apart from SysJobServers which hold information on when the job last run and the outcome).

Again, information on the fields can be found at MSDN. For example, check out the page for SysJobs