Post reply

How do I identify currently executing jobs in SQL Server Agent using T-SQL

  • April 25, 2004 at 5:05 pm

    #108046

    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

  • April 25, 2004 at 7:39 pm

    #504164

    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.

  • April 25, 2004 at 9:54 pm

    #504168

    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

  • April 26, 2004 at 10:48 am

    #504254

    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