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?


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'
  • 1
    Thanks, slight change to get just the data I need, EXEC msdb..sp_help_job @job_name = 'job name' ,@job_aspect = N'job' – Swinders Feb 8 '10 at 13:31

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.

  • Using msdb..sysjobhistory I can see if a job or step has executed and the outcome but I can't see if it is currently executing. – Swinders Feb 8 '10 at 13:30

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
select text, * from sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 order by 1
  • 2
    you can't use those dmv's in sql server 2000 – Nick Kavadias Feb 5 '10 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.