Job Status in SQL 2005

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

You haven't specified how would you like to see these details.

For the first sight I would suggest to check Server Management Studio.

You can see the jobs and current statuses in the SQL Server Agent part, under Jobs. If you pick a job, the Property page shows a link to the Job History, where you can see the start and end time, if there any errors, which step caused the error, and so on.

You can specify alerts and notifications to email you or to page you when the job finished successfully or failed.

There is a Job Activity Monitor, but actually I never used it. You can have a try.

If you want to check it via T-SQL, then I don't know how you can do that.

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

Answer 3

This is what I'm using to get the running jobs (principally so I can kill the ones which have probably hung):

select job.Name, job.job_ID, job.Originating_Server, 
activity.run_requested_Date, datediff(minute, activity.run_requested_Date, getdate()) as Elapsed
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity
on (job.job_id = activity.job_id)
where run_Requested_date is not null and stop_execution_date is null
and job.name like 'Your Job Prefix%'

As Tim said, the MSDN / BOL documentation is reasonably good on the contents of the sysjobsX tables. Just remember they are tables in MSDB...