SQL Job handling using T SQL

EXEC msdb.dbo.sp_start_job N'Weekly Accounts Payable Transaction History';

SELECT * FROM msdb.dbo.sysjobs ORDER BY name

-- STOP a currently executing job - sql server agent stored procedure

EXEC msdb.dbo.sp_stop_job N'NightlyInventoryFeed' ;

-- Get job INFO on application job - execute system stored procedure

EXEC msdb.dbo.sp_help_job

    @job_name = N'FinanceBackup',

    @job_aspect = N'ALL' ;

GO

-- SQL Server Agent disable job

------------

EXEC msdb.dbo.sp_update_job

    @job_name = N'Back Up Database - AdventureWorks2008',

    @enabled = 0 ;

Check running status of a job

-- Check run status of a job

-- SQL Server 2008 T-SQL - Running = 1 means currently executing

DECLARE @job_id uniqueidentifier = '4C004358-62E0-4933-9268-90F54EF36AA9' 

EXEC master.dbo.xp_sqlagent_enum_jobs 1, sa, @job_id

-- If current_execution_status=1 then RUNNING
EXEC msdb.dbo.sp_help_job  @job_id='0D823348-2DDC-4816-93EA-307D6E736437'

-- SQL Server basic job history information for rolling month
------------
SELECT   JobName, 
         RunTime, 
         Duration, 
         Status, 
         Server 
FROM     (SELECT JobName = job_name, 
                 RunTime = run_datetime, 
                 Duration = SUBSTRING(run_duration,1,2) + ':' + 
                            SUBSTRING(run_duration,3,2) + ':' + 
                            SUBSTRING(run_duration,5,2), 
                 Status = run_status, 
                 Server = [server] 
          FROM   (SELECT j.name AS job_name, 
                         run_datetime = CONVERT(DATETIME,RTRIM(run_date)) + 
                         (run_time * 9 + run_time%10000 * 6 + 
                          run_time%100 * 10) / 2160000, 
                         run_duration = RIGHT('000000' + 
                         CONVERT(VARCHAR(6),run_duration), 6), 
                         run_status = CASE run_status 
                                        WHEN 0 THEN 'Failure' 
                                        WHEN 1 THEN 'Success' 
                                        WHEN 2 THEN 'Retry' 
                                        WHEN 3 THEN 'Cancelled' 
                                        WHEN 4 THEN 'Running' 
                                        ELSE 'Other: ' + 
                                        Convert(VARCHAR,run_status) 
                                      END, 
                         server 
                  FROM   msdb.dbo.sysjobhistory h 
                         INNER JOIN msdb.dbo.sysjobs j 
                           ON h.job_id = j.job_id 
                  WHERE  step_id = 0) x) z 
WHERE    DATEDIFF(mm,RunTime,getdate()) < 1

ORDER BY RunTime DESC

-- SQL Server Agent detailed job information - more columns can be added as needed

------------

SELECT job.name                                      AS JobName,

       job.job_id,

       job.enabled                                   AS JobEnabled,

       CONVERT(SYSNAME,SERVERPROPERTY('Servername')) AS Server,

       job.DESCRIPTION,

       job.notify_level_eventlog,

       job.notify_level_email,

       job.notify_level_page,

       job.notify_email_operator_id,

       job.date_created,

       cat.name                                      AS CategoryName,

       sch.next_run_date,

       sch.next_run_time,

       srv.last_run_outcome,

       srv.last_outcome_message,

       srv.last_run_date,

       srv.last_run_time,

       srv.last_run_duration,

       op.name                                       AS NotifyOperatorName,

       op.email_address,

       job.date_modified                             AS JobDateModified,

       ssch.name                                     AS ScheduleName,

       ssch.enabled                                  AS ScheduleEnabled,

       ssch.freq_type,

       ssch.freq_interval,

       ssch.freq_subday_interval,

       ssch.freq_subday_type,

       ssch.freq_relative_interval,

       ssch.freq_recurrence_factor,

       ssch.active_start_date,

       ssch.active_end_date,

       ssch.active_start_time,

       ssch.active_end_time,

       GETDATE()                                     AS ThisReportRunDate

FROM   msdb.dbo.sysjobs job

       INNER JOIN msdb.dbo.syscategories cat

         ON job.category_id = cat.category_id

       LEFT OUTER JOIN msdb.dbo.sysoperators op

         ON job.notify_page_operator_id = op.id

       LEFT OUTER JOIN msdb.dbo.sysjobservers srv

         ON job.job_id = srv.job_id

       LEFT OUTER JOIN msdb.dbo.sysjobschedules sch

         ON sch.job_id = job.job_id

       LEFT OUTER JOIN msdb.dbo.sysschedules ssch

         ON sch.schedule_id = ssch.schedule_id

ORDER BY JobName