Use T-SQL to find a job that takes too long to execute

Source: Use T-SQL to find jobs that take too long

Sometimes, some jobs run into problems, so I wrote a script that can track the history, find out how long it takes to perform, and find these jobs in a timely manner and resolve them as soon as possible, with the following code:

SELECT Sj.name

   , Sja.start_execution_date,datediff (SECOND as Executedmin,ja. Avgruntimeonsucceed

 from  as Sja

INNER JOIN  as  on INNER

Join

(

    SELECT job_id,

    Avg

    ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100)

    +

    Nullif (0,stdev

     as ' Avgruntimeonsucceed '

      from Msdb.dbo.sysjobhistory

    WHERE  and run_status = 1

    GROUP  by

     on sj.job_id = ja.job_id

WHERE  is  not NULL --The job has a start

    and  is NULL --The job is not over

    and Sja.start_execution_date>dateadd (Day, -2,getdate ())--Jobs start within 2 days

   and DATEDIFF (SECOND , Sja.start_execution_date,getdate ()) >ja. Avgruntimeonsucceed *1.5--Job execution time is 50% more than the historical average time

If the job frequently has problems, you can use the script to monitor early detection of problems.

Use T-SQL to find a job that takes too long to execute