SQL Server Jobs

PROBLEM:
You need some useful scripts for running SQL Server jobs, see status, etc.

SOLUTION:
-- Start job from script

EXEC msdb.dbo.sp_start_job N'DB_Backup';

-- List all jobs with basic info like job_id, name, etc.

SELECT * FROM msdb.dbo.sysjobs ORDER BY name

-- STOP a currently executing job

EXEC msdb.dbo.sp_stop_job N'DB_Backup' ;

-- Get job INFO on application job

EXEC msdb.dbo.sp_help_job

    @job_name = N'DB_Backup',

    @job_aspect = N'ALL' ;

-- Check run status of a job. Running = 1 means currently executing

DECLARE @job_id uniqueidentifier = 'B6371A83-93EC-4176-AB95-0423B700E880'

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 Agent disable job

EXEC msdb.dbo.sp_update_job

    @job_name = N'DB_Backup',

    @enabled = 0 ;

-- 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

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

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!