Post reply

Agent Job history step duration information

  • September 16, 2020 at 6:11 pm

    #3788925

    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,

     

  • September 16, 2020 at 7:35 pm

    #3788931

    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.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

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

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