Have you ever lost track of which big jobs run when on a SQL Server instance? I did.

We have some maintenance jobs that run overnight. over time we’ve removed some, added others, and generally not kept good records of which time slots were reserved for what.

Schedule

Adjusting the schedule

This query uses records from the sysjobhistory table to determine which jobs run for longer than 5 minutes, when they started, and roughly when they’ve been finishing.

with

JobNameStartTimeStdDevDuration as (
select
  j.name,
  --h.run_date,
  cast(
    substring(right('000000' + rtrim(h.run_time), 6),1,2) + ':' +
    substring(right('000000' + rtrim(h.run_time), 6),3,2) + ':' +
    substring(right('000000' + rtrim(h.run_time), 6),5,2)
  as time(0)) as start_time,
  avg((floor(h.run_duration/10000) * 60 + floor(h.run_duration/100) % 100) * 60
    + h.run_duration % 100) as AvgDuration,
  stdev((floor(h.run_duration/10000) * 60 + floor(h.run_duration/100) % 100) * 60
    + h.run_duration % 100) as StdDevDuration,
  count(*) as RecordCount

from msdb.dbo.sysjobs j
join msdb.dbo.sysjobhistory h
on j.job_id=h.job_id

where h.step_id=0 -- Entire job

group by
  j.name,
  cast(
    substring(right('000000' + rtrim(h.run_time), 6),1,2) + ':' +
    substring(right('000000' + rtrim(h.run_time), 6),3,2) + ':' +
    substring(right('000000' + rtrim(h.run_time), 6),5,2)
  as time(0))

-- Look at jobs running longer than 5 minutes on average
having avg((floor(h.run_duration/10000) * 60 + floor(h.run_duration/100) % 100) * 60
  + h.run_duration % 100) > 300
-- and which have run more than one time
and count(*) > 1
)

select
  name,
  start_time,
  cast(dateadd(second, AvgDuration, start_time) as time(0)) as end_time_avg,
  cast(dateadd(second, AvgDuration + 2 * StdDevDuration, start_time) as time(0))
    as end_time_two_stdev,
  RecordCount
from JobNameStartTimeStdDevDuration
order by 2,3 desc
;

The query results look like this.

LongRunningSqlServerAgentJobs

I have only shown the first few rows of the result set.

At this point I copied the result set to Excel and graphed the mean start and end times of day. That showed me where the conflicts are, so I could adjust the schedules. It also proved to be a good tool to communicate the conflicts with others.

Hope this is helpful!

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available