Restarting SQL Server Agent

Before restarting SQL Server Agent, find out if there are running jobs.
Here are some slightly modified scripts from this answer.

-- find jobs that are currently executing
SELECT sj.name, sja.*
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj
ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
AND sja.stop_execution_date IS NULL;

If there are no running jobs, find out if there are jobs about to start within the next hour:

-- find jobs that are going to execute within the next hour
select sj.name, sja.*
from msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj
ON sja.job_id = sj.job_id
where next_scheduled_run_date >= getdate()
and next_scheduled_run_date <= dateadd(HOUR,1, getdate());

To restart SQL Server Agent, go to SQL Server Configuration Manager => SQL Server Services => SQL Server Agent

Leave a Reply

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