Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

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'
share|improve this answer
    
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
add comment

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.

share|improve this answer
    
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
add comment

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
share|improve this answer
2  
you can't use those dmv's in sql server 2000 –  Nick Kavadias Feb 5 '10 at 14:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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