Agent Jobs Starting Simultaneously

sp_Blitz® Result: Agent Jobs Starting Simultaneously

SQL Server Agent is a job scheduler used for backups, index maintenance, and user-created jobs. Ideally, we schedule all of our jobs so that they’re staggered throughout the day, but sometimes accidents happen. Sometimes we accidentally set up multiple jobs to kick off at exactly the same time, putting our SQL Server under heavy load for short bursts of time. (Our favorite case was over a hundred reporting jobs starting simultaneously every hour.)

This part of our SQL Server sp_Blitz® script checks msdb.dbo.sysjobactivity looking for multiple jobs starting at exactly the same time in the last 2 weeks.

To FIX THE PROBLEM

Query the full detail of Agent jobs to see which jobs are scheduled to run simultaneously:

SELECT j.name, j.description, a.start_execution_date
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobactivity a ON j.job_id = a.job_id
WHERE a.start_execution_date > DATEADD(dd, -14, GETDATE())
AND j.enabled = 1
AND a.start_execution_date IN
(SELECT start_execution_date
FROM msdb.dbo.sysjobactivity
WHERE start_execution_date > DATEADD(dd, -14, GETDATE())
GROUP BY start_execution_date HAVING COUNT(*) > 1)
ORDER BY 3

SELECT j.name, j.description, a.start_execution_date

FROM msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobactivity a ON j.job_id = a.job_id

WHERE a.start_execution_date > DATEADD(dd, -14, GETDATE())

AND j.enabled = 1

AND a.start_execution_date IN

(SELECT start_execution_date

FROM msdb.dbo.sysjobactivity

WHERE start_execution_date > DATEADD(dd, -14, GETDATE())

GROUP BY start_execution_date HAVING COUNT(*) > 1)

ORDER BY 3

Then start tweaking the job schedules to smooth out the load on the SQL Server.

Return to sp_Blitz or Ask Us Questions