Agent Job history step duration information

  • Hi,

    I wanted to export the information such as how much time the EACH step is taking to execute for a specific agent job.

    I have a job which has some 25 steps and I can see in the history how much time the step is taking however can we export history information in excel so that I can filter the more time taking steps? I only see the export option in the log format.

    Please share any script or advice. I want to look and filter the steps which are taking more than say 30mins for a specific job for a month.

    Thanks,

     

  • I think this script will do it:

    USE [msdb];
    GO

    SELECT
    [name]
    , [step_id]
    , [step_name]
    , [message]
    , [run_status]
    , [run_date]
    , [run_time]
    , [run_duration]
    FROM [dbo].[sysjobhistory]
    JOIN [dbo].[sysjobs]
    ON [sysjobs].[job_id] = [sysjobhistory].[job_id]
    WHERE [step_id] > 0;

    Just add an extra part on the WHERE clause to capture your run_duration limit.  That will tell you all jobs that have run on the server and when they ran, how long they ran for, etc.  It shows basically the same thing that you'd see from the job history.  You could filter that down to a specific job if you wanted as well by filtering on sysjobs.name or a specific job step.

    As for exporting that to Excel, in Excel you can set SQL as a data source, put that query in (remove the USE [msdb] GO part and set the connection to be on msdb though) and then whenever you click on "Refresh All" on the data tab, it will pull in the current information.

    I did where step_id > 0 because step id of 0 is for the job; you may want to include that.

Viewing 2 posts - 1 through 2 (of 2 total)

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