Helpful sql commands to manage SQL Agent Jobs

Quite often you may not be available 24 x 7 , and need to delegate some actions for SQL Agent jobs to developers or Operations staff.

The typical range of tasks in managing SQL Agent tasks can vary , but this list normally covers the range for the accidental | reluctant individual | DBA who needs to manage SQL Agent Jobs using t-sql

--start the job manually

--stop the job manually

--check if the job is running i.e if it's not in the list it's not running

--disable job

--enable job

--check the job last run time & finish time

--start the job manually
EXEC msdb.dbo.sp_start_job N'my_job' ;

--stop the job manually
EXEC msdb.dbo.sp_stop_job N'my_job' ;

--check if the job is running i.e if it's not in the list it's not running 
EXEC msdb.dbo.sp_help_job @execution_status=1 

--disable job
EXEC msdb.dbo.sp_update_job @job_name='my_job',@enabled = 0

--enable job
EXEC msdb.dbo.sp_update_job @job_name='my_job',@enabled = 1

--check the job last run time & finish time 
WITH jobhistory as (   
   SELECT    job_id,
             run_status,
             last_run_time = max(dbo.agent_datetime(run_date, run_time))
   FROM      msdb.dbo.sysjobhistory
   WHERE     step_id = 0
             AND run_status = 1
   GROUP BY job_id, run_status)

SELECT    j.job_id,
          j.name,
          jh.run_status,
          jh.last_run_time
FROM      msdb.dbo.sysjobs j
          LEFT OUTER JOIN jobhistory jh
          ON j.job_id = jh.job_id
          where name = 'my_job'
ORDER BY j.name ASC

Read More

Send sql server agent job history through email notification

List enabled status of SQL Server Agent Jobs (SQL Server DBA)

SQL Agent Jobs – Schedule in seconds (SQL Server DBA)

Manage SQL Agent jobs without sysadmin (SQL Server DBA)