Helpful sql commands to manage SQL Agent Jobs

03 February,2017 by Jack Vamvas

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)

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Helpful sql commands to manage SQL Agent Jobs


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer