Custom Alert: Need query for Job Executing

• 
  Hi Stephen,
  
  You can try plugging this query into a custom metric against the MSDB database. It will report the number of seconds a job named MyJob has been running. You should then be able to set an alert when the number of seconds has been exceeded. I hope this helps!

  /* Get the JobID for MyJob */
  DECLARE @JobId UNIQUEIDENTIFIER
  SELECT @JobId= job_id FROM msdb..sysjobs WHERE name='MyJob'
  /* Define a temp table to hold xp_sqlagent_enum_jobs
  This xp will tell us which jobs are running */
  DECLARE @ExecutionStatus table 
   ( 
  	JobID uniqueidentifier primary key, -- Job ID 
  	LastRunDate int, LastRunTime int, -- Last run date and time 
  	NextRunDate int, NextRunTime int, -- Next run date and time 
  	NextRunScheduleID int, -- an internal schedule id 
  	RequestedToRun int, RequestSource int, RequestSourceID varchar(128), 
  	running int,  -- 0 or 1, 1 means the job is executing 
  	CurrentStep int, -- which step is running 
  	CurrentRetryAttempt int, -- retry attempt 
  	JobState int ----0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions 
   ) 
  INSERT @ExecutionStatus
          EXEC xp_sqlagent_enum_jobs 1, '', @JobId
          /* Get the number of seconds MyJob has been running, if it is running */
  		SELECT DATEDIFF(ss,a.start_execution_date,GETDATE()) AS RunningSeconds FROM msdb..sysjobactivity a INNER JOIN @ExecutionStatus s ON a.job_id=s.JobId WHERE s.running=1
  

  Brian Donahue September 19, 2012 08:55

  - 0 +
• 
  Helps IMMENSELY.
  
  Many thanks, Brian.
  
  UPDATE
  Sadly, the join criterion on just job_id gets me the list of every execution, so I revised the query, using my own replacement for DATEDIFF() for the hours calc as I want to be precise, not just have it fire after 3hrs 1min if it starts at, say, 12:59... (Hate DATEDIFF!)
  
  Using SELECT TOP 1 ... ORDER BY a.start_execution_date DESC gets me the currently executing Job details.

  /* Get the JobID for MyJob */ 
  DECLARE @JobId uniqueidentifier 
  
  SELECT @JobId = job_id
    FROM msdb..sysjobs
   WHERE name = '_Quarter Hourly Update Aggregation' 
  /* Define a temp table to hold xp_sqlagent_enum_jobs 
  This xp will tell us which jobs are running */ 
  DECLARE @ExecutionStatus TABLE
        ( JobID                 uniqueidentifier PRIMARY KEY -- Job ID 
        , LastRunDate           int
        , LastRunTime           int -- Last run date and time 
        , NextRunDate           int
        , NextRunTime           int -- Next run date and time 
        , NextRunScheduleID     int -- an internal schedule id 
        , RequestedToRun        int
        , RequestSource         int
        , RequestSourceID       varchar(128)
        , running               int -- 0 or 1, 1 means the job is executing 
        , CurrentStep           int -- which step is running 
        , CurrentRetryAttempt   int -- retry attempt
        , JobState              int ----0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions 
        ) 
  INSERT @ExecutionStatus
    EXEC xp_sqlagent_enum_jobs 1
                             , ''
                             , @JobId 
  /* Get the number of hours the has been running, if it is running */ 
  SELECT TOP 1
         DBA.dbo.udf_GetElapsedHours(a.start_execution_date, GETDATE()) AS RunningHours
    FROM msdb..sysjobactivity a
         INNER  JOIN @ExecutionStatus s
                  ON s.JobId = a.job_id
   WHERE s.running = 1
   ORDER BY a.start_execution_date DESC
  

  
  Thanks, though, for your original query - wouldn't be anywhere without it. 8)

  PDinCA September 19, 2012 12:48

  - 0 +
• 
  As there's no Uservoice (that I can see), could I register this post as an Enhancement Request?
  
  As it is, I can use the above for ONE Job per Custom Alert, which is fine if that's all one cared about, up to a handful perhaps would be OK, but in environments with many servers and many, many Jobs, I can see this kind of thing being unwieldy in a hurry.
  
  Would RG please consider adding a standard alert "Job Still Running"? It should be reusable by being able to select the job of interest and state the job duration that when exceeded would raise the alert. Alert thresholds could use the standard Low/Medium/High measures for "Duration in excess of the Alert Duration", perhaps.
  
  Thanks in hopes.

  PDinCA September 19, 2012 14:18

  - 0 +
• 
  Hi Stephen,
  
  I'm glad I could help. I'm surprised that you can't just get this information from sysjobhistory, but that table only updates after the job finishes. So you have to use an undocumented xp to get the running jobs and then check the job activity table.
  
  Sql Monitor has a UserVoice site here in case you want to add suggestions:
  
  http://sqlmonitor.uservoice.com/forums/ ... uggestions

  Brian Donahue September 20, 2012 04:09

  - 0 +
• 
  Thanks for the Uservoice URL, Brian, I added to the very similar suggestion titled, "specify a time in which jobs should be finished by" at http://sqlmonitor.uservoice.com/forums/91743-suggestions/suggestions/2708577-specify-a-time-in-which-jobs-should-be-finished-by
  
  Would you please post the RSS URL for the SSC Uservoice as I like to keep an eye on suggestions that way (I already have the SQL Prompt, SSC and Search RSS feeds active). Thanks.

  PDinCA September 20, 2012 13:06

  - 0 +

Add comment

Please sign in to leave a comment.