How can I get the status of an sql agent job?

Question

We use an sql agent job running on our SQL 2000 server to import data from our business system every ten minutes. The job runs a number of steps and usually takes about 5 to 6 minutes to run. This data is used for various packing processes.

This will usually run with no problems but on occasion we have problems where a step will get 'stuck' which means we do not get new data from the business system.

I would like to display the status of the job on a screen in our office so we can see if there is a problem. Is there a query I can perform on the database to return the status of a job, if it is running, which step it's on, how long it's been running and when it is next scheduled to run?

Answer 1

The code below will give you the status of all jobs and should be supported going forward, otherwise using the msdb..sysjobhistory should do the trick.

exec msdb..sp_help_job -- gives you all jobs and a status for each
exec msdb..sp_help_job @job_id = 'job_id GUID from sysjobs'
exec msdb..sp_help_job @job_name = 'job name'

Answer 2

SQL Server Agent logs job data to msdb..sysjobhistory You can use that table to track the success/failure of the job and job steps & see if its running.

You can use msdb..sysjobschedules to work out next scheduled run time.

Answer 3

I use the following query often to determine what is currently running on a SQL server. It won't be specific to what agent is running but it gives you an idea of what specific query or queries are currently executing:

USE Master
GO
select text, * from sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 order by 1
GO