Sql-server – How to get the status of an sql agent job

sql server

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?

Best Answer

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'
Related Topic