Monitoring Long Running Agent Jobs

  • Hi

    Does anybody have a way of monitoring for long running sql agent jobs? I've looked at all the usual suspects, sp_help_job, xp_sqlagent_enum_jobs, job tables etc but none of them seem to tell me how long a job has been running or even when it started (it must be held somewhere mustn't it?)

    We had a situation were a sql agent job (calling a dts package that executes a batch file) had been running for days waiting for a response from a RSH to a mainframe). Nobody knew because it didn't fail.

    I know I can set the timeout for a batch file execution from within the DTS package, but I would prefer to monitor the execution of the Agent job.

    Thanks

    Andy

  • You will need a bespoke script to that I think.

    Check out the link below.

    http://databasejournal.com/features/mssql/article.php/2168291

  • You'll need a bit of T-SQL coding

    First you'll need to record the previous run duration. Maybe store an average value for past executions.

    Record when a job starts

    Then use xp_sqlagent_enum_jobs to determine which jobs are running.

    For the jobs that are running, compare the datediff from the start time against your stored duration.

    Have a browse throught the script library and you might find something useful there.

     

    --------------------
    Colt 45 - the original point and click interface

  • Thanks Phill

    My problem is that I can't seem to find where the job start time is stored. Any ideas?

    Andy

  • Hi Andy,

    I use this script to capture info on job/job step duration

    DECLARE @JOBNAME varchar(200)

    SET @JOBNAME = 'YoUR Job Name'

    SELECT DISTINCT j.name [JOB], jt.Step_ID [StepID],jt.Step_Name [Step],

     SUBSTRING(CAST(jt.Last_Run_Duration AS varchar(10)) ,LEN(CAST(jt.Last_Run_Duration AS varchar(10)))-5,2) [Hours],

     SUBSTRING(CAST(jt.Last_Run_Duration AS varchar(10)) ,LEN(CAST(jt.Last_Run_Duration AS varchar(10)))-3,2) [Minutes],

     RIGHT(jt.Last_Run_Duration,2)[Seconds],jt.Last_Run_Date [Last Run Date],jt.Last_Run_Time [Time],GetDate() [Check Date]

    FROM msdb.dbo.sysjobs j

     INNER JOIN msdb.dbo.sysjobschedules js ON js.job_Id = j.job_id

     INNER JOIN msdb.dbo.sysjobsteps jt ON jt.job_id = j.job_id

    WHERE j.name = @JobName

    ORDER BY [StepID]

    Enter the name of your job in the job name variable and run this against the MSDB databse.

    Any thoughts?

    Graeme

     

  • Thanks Graeme

    I tried this but it only gives me info on the last run of the job, not how long the job has been running if it is currently running.

    Andy

  • I've found a solution here http://www.sqldts.com/default.aspx?271

    Thanks for your thoughts and replies.

    Andy

  • Hi,

    For SQL 2005 or after, you can get current job execution time from the view

    msdb.dbo.sysjobactivity.

  • Graeme, I like your suggestion. However, I don't think you need to have "INNER JOIN msdb.dbo.sysjobschedules AS js ON js.job_id = j.job_id" in your statement, since you don't actually use any of the fields. The reason I bring this up is that we have jobs with multiple schedules, and your results get funky if the job has multiple schedules.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply