How do I identify currently executing jobs in SQL Server Agent using T-SQL
-
Calling All Experts:
I need help identifying jobs that are currently executing in SQL Server Agent from within a T-SQL script.
I have tried running the following T-SQL statement in SQL Analyzer but the run_status field in the sysjobhistory table never equals 4. I looked in Books Online and it says that a status of 4 indicates that a job is in progress.
Select *
From sysjobhistory
where run_status = 4
If anyone can shed some light on how I can determine the status of currently running jobs I would appreciate your help.
Thanks,
Thomas
-
I've only seen completed jobs/steps in the job history table.
Most of sql agent happens outside of sql server. Take a look at sp_help_job. I think it gets job status by calling some extended stored procedure. Try a temp table and the right parameters in an INSERT/EXEC sp_help_job statement.
-
Take a look at sp_help_job. This proc calls xp_sqlagent_enum_jobs which determines the jobs current status.
--------------------
Colt 45 - the original point and click interface
-
Try this snippet:
EXEC msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply