Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Answered: Currently Running Jobs

    Knocked a script together today to show me currently running jobs by [ab]using xp_sqlagent_enum_jobs
    Code:
    IF Object_ID('tempdb..#jobs', 'U') IS NOT NULL
      BEGIN
        DROP TABLE #jobs;
      END
    ;
    
    CREATE TABLE #jobs (
       job_id                uniqueidentifier
     , last_run_date         nvarchar (20)
     , last_run_time         nvarchar (20)
     , next_run_date         nvarchar (20)
     , next_run_time         nvarchar (20)
     , next_run_schedule_id  int
     , requested_to_run      int
     , request_source        int
     , request_source_id     sysname NULL
     , is_running            bit
     , current_step          int
     , current_retry_attempt int
     , job_state             int
    );
    
    INSERT INTO #jobs (job_id, last_run_date, last_run_time, next_run_date, next_run_time, next_run_schedule_id, requested_to_run, request_source, request_source_id, is_running, current_step, current_retry_attempt, job_state)
    EXEC master.dbo.xp_sqlagent_enum_jobs
         @is_sysadmin    = 1
       , @job_owner_name = ''
    ;
    
    ; WITH job_info AS (
      SELECT jobs.name As job_name
           , jobs.description As job_description
           , jobs."enabled" As is_enabled
           , x.next_run_schedule_id
           , x.requested_to_run
           , x.request_source
           , x.request_source_id
           , x.is_running
           , x.current_step
           , x.current_retry_attempt
           , Cast(Stuff(Stuff(x.last_run_date, 5, 0, '-'), 8, 0, '-') + ' ' + Stuff(Stuff(Right('000000' + x.last_run_time, 6), 3, 0, ':'), 6, 0, ':') As datetime) As last_run
           , Cast(Stuff(Stuff(x.next_run_date, 5, 0, '-'), 8, 0, '-') + ' ' + Stuff(Stuff(Right('000000' + x.next_run_time, 6), 3, 0, ':'), 6, 0, ':') As datetime) As next_run
           , CASE x.job_state
               WHEN 0 THEN  'Not idle or suspended'
               WHEN 1 THEN  'Executing'
               WHEN 2 THEN  'Waiting For Thread'
               WHEN 3 THEN  'Between Retries'
               WHEN 4 THEN  'Idle'
               WHEN 5 THEN  'Suspended'
               WHEN 6 THEN  'Waiting for Step to Finish'
               WHEN 7 THEN  'Performing Completion Actions'
               ELSE 'Unknown'
             END As job_state_description
           , x.job_id
      FROM   #jobs As x
       LEFT
        JOIN msdb.dbo.sysjobs As jobs
          ON jobs.job_id = x.job_id
    )
    SELECT job_name
         , is_running
         , last_run
         , next_run
         , job_state_description
         , CASE WHEN is_running = 1 THEN
             CASE WHEN DateDiff(ss, last_run, Current_Timestamp) > 24 * 60 * 60 THEN
               Cast(DateDiff(dd, last_run, Current_Timestamp) As varchar(11)) + 'd ' /* yes, I really do have to cater for this :rolleyes: */
             ELSE
               ''
             END
             + Cast(Cast(DateAdd(ss, DateDiff(ss, last_run, Current_Timestamp), 0) As time) As char(8))
           END As current_run_duration
         , CASE WHEN is_running = 1 THEN
             DateDiff(ss, last_run, Current_Timestamp)
           END As current_run_duration_seconds
    FROM   job_info
    WHERE  is_running = 1
    ORDER
        BY current_run_duration DESC
         , job_name
    ;
    But I fear that I've got the logic wrong for the current_run_duration, as I am using the last_run field as the basis.

    I'm getting a mismatch between the calculated run duration and the time taken shown in the job history in SSMS.

    Thoughts?
    George
    Home | Blog

  2. Best Answer
    Posted by MCrowley

    "You may need to include the sysjobactivity table to get the current requested run, rather than the last successful run. I have not played with this table long enough. I have a vague memory of being in the position where neither xp_sqlagent_enum_jobs nor sysjobactivity had what I was looking for at the time, but that may have been a few versions of SQL ago."


  3. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You may need to include the sysjobactivity table to get the current requested run, rather than the last successful run. I have not played with this table long enough. I have a vague memory of being in the position where neither xp_sqlagent_enum_jobs nor sysjobactivity had what I was looking for at the time, but that may have been a few versions of SQL ago.

  4. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Cheers dude, I will check it out.

    I'm trying to be a perfectionist, but perhaps it's not a problem.
    This is only being implemented to watch for jobs that have taken more than an hour to execute.
    Reason for this passive checking is because we had a job, written and implemented by a 3rd party, that ran for over 15 days (yes, seriously. They don't code in sets) and I've been asked to implement something to try spot this before it causes bigger headaches.
    George
    Home | Blog

  5. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yep, sysjobactivity looks to do the trick.
    I'm just grabbing the latest start_execution_date per job_id and using that for my calculations.
    Seems to provide much more palatable results!
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •