Querying for Failed Jobs

SET  NOCOUNT ON
 
DECLARE @MaxLength   INT
SET @MaxLength   = 50
 
DECLARE @xp_results TABLE (
                       job_id uniqueidentifier NOT NULL,
                       last_run_date nvarchar (20) NOT NULL,
                       last_run_time nvarchar (20) NOT NULL,
                       next_run_date nvarchar (20) NOT NULL,
                       next_run_time nvarchar (20) NOT NULL,
                       next_run_schedule_id INT NOT NULL,
                       requested_to_run INT NOT NULL,
                       request_source INT NOT NULL,
                       request_source_id sysname
                             COLLATE database_default NULL,
                       running INT NOT NULL,
                       current_step INT NOT NULL,
                       current_retry_attempt INT NOT NULL,
                       job_state INT NOT NULL
                    )
 
DECLARE @job_owner   sysname
 
DECLARE @is_sysadmin   INT
SET @is_sysadmin   = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner   = suser_sname ()
 
INSERT INTO @xp_results
   EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
 
UPDATE @xp_results
   SET last_run_time    = right ('000000' + last_run_time, 6),
       next_run_time    = right ('000000' + next_run_time, 6)
 
SELECT j.name AS JobName,
       j.enabled AS Enabled,
       CASE x.running
          WHEN 1
          THEN
             'Running'
          ELSE
             CASE h.run_status
                WHEN 2 THEN 'Inactive'
                WHEN 4 THEN 'Inactive'
                ELSE 'Completed'
             END
       END
          AS CurrentStatus,
       coalesce (x.current_step, 0) AS CurrentStepNbr,
       CASE
          WHEN x.last_run_date > 0
          THEN
             convert (datetime,
                        substring (x.last_run_date, 1, 4)
                      + '-'
                      + substring (x.last_run_date, 5, 2)
                      + '-'
                      + substring (x.last_run_date, 7, 2)
                      + ' '
                      + substring (x.last_run_time, 1, 2)
                      + ':'
                      + substring (x.last_run_time, 3, 2)
                      + ':'
                      + substring (x.last_run_time, 5, 2)
                      + '.000',
                      121
             )
          ELSE
             NULL
       END
          AS LastRunTime,
       CASE h.run_status
          WHEN 0 THEN 'Fail'
          WHEN 1 THEN 'Success'
          WHEN 2 THEN 'Retry'
          WHEN 3 THEN 'Cancel'
          WHEN 4 THEN 'In progress'
       END
          AS LastRunOutcome,
       CASE
          WHEN h.run_duration > 0
          THEN
               (h.run_duration / 1000000) * (3600 * 24)
             + (h.run_duration / 10000 % 100) * 3600
             + (h.run_duration / 100 % 100) * 60
             + (h.run_duration % 100)
          ELSE
             NULL
       END
          AS LastRunDuration
  FROM          @xp_results x
             LEFT JOIN
                msdb.dbo.sysjobs j
             ON x.job_id = j.job_id
          LEFT OUTER JOIN
             msdb.dbo.syscategories c
          ON j.category_id = c.category_id
       LEFT OUTER JOIN
          msdb.dbo.sysjobhistory h
       ON     x.job_id = h.job_id
          AND x.last_run_date = h.run_date
          AND x.last_run_time = h.run_time
          AND h.step_id = 0

Query Test Checklist

• Works on SQL Server 2008: Yes
• Works on SQL Server 2005: Yes
• Works on SQL Server 2000: No
• Works on Standard Edition: Yes
• Works on case-sensitive servers: Yes

Tests Updated by Brent Ozar, 2009-04-01

Retrieve and Disable All Active Jobs

DECLARE @weekDay TABLE (
      mask      INT
    , maskValue VARCHAR(32)
);
 
INSERT INTO @weekDay
SELECT 1, 'Sunday'  UNION All
SELECT 2, 'Monday'  UNION All
SELECT 4, 'Tuesday'  UNION All
SELECT 8, 'Wednesday'  UNION All
SELECT 16, 'Thursday'  UNION All
SELECT 32, 'Friday'  UNION All
SELECT 64, 'Saturday';
 
WITH myCTE
AS(
    SELECT sched.name AS 'scheduleName'
        , sched.schedule_id
        , jobsched.job_id
        , CASE WHEN sched.freq_type = 1 THEN 'Once' 
            WHEN sched.freq_type = 4 
                And sched.freq_interval = 1 
                    THEN 'Daily'
            WHEN sched.freq_type = 4 
                THEN 'Every ' + CAST(sched.freq_interval AS VARCHAR(5)) + ' days'
            WHEN sched.freq_type = 8 THEN 
                REPLACE( REPLACE( REPLACE(( 
                    SELECT maskValue 
                    FROM @weekDay AS x 
                    WHERE sched.freq_interval & x.mask <> 0 
                    ORDER BY mask FOR XML Raw)
                , '"/>', '') 
                + CASE WHEN sched.freq_recurrence_factor <> 0 
                        And sched.freq_recurrence_factor = 1 
                            THEN '; weekly' 
                    WHEN sched.freq_recurrence_factor <> 0 THEN '; every ' 
                + CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' weeks' END
            WHEN sched.freq_type = 16 THEN 'On day ' 
                + CAST(sched.freq_interval AS VARCHAR(10)) + ' of every '
                + CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' months' 
            WHEN sched.freq_type = 32 THEN 
                CASE WHEN sched.freq_relative_interval = 1 THEN 'First'
                    WHEN sched.freq_relative_interval = 2 THEN 'Second'
                    WHEN sched.freq_relative_interval = 4 THEN 'Third'
                    WHEN sched.freq_relative_interval = 8 THEN 'Fourth'
                    WHEN sched.freq_relative_interval = 16 THEN 'Last'
                END + 
                CASE WHEN sched.freq_interval = 1 THEN ' Sunday'
                    WHEN sched.freq_interval = 2 THEN ' Monday'
                    WHEN sched.freq_interval = 3 THEN ' Tuesday'
                    WHEN sched.freq_interval = 4 THEN ' Wednesday'
                    WHEN sched.freq_interval = 5 THEN ' Thursday'
                    WHEN sched.freq_interval = 6 THEN ' Friday'
                    WHEN sched.freq_interval = 7 THEN ' Saturday'
                    WHEN sched.freq_interval = 8 THEN ' Day'
                    WHEN sched.freq_interval = 9 THEN ' Weekday'
                    WHEN sched.freq_interval = 10 THEN ' Weekend'
                END
                + CASE WHEN sched.freq_recurrence_factor <> 0 
                        And sched.freq_recurrence_factor = 1 THEN '; monthly'
                    WHEN sched.freq_recurrence_factor <> 0 THEN '; every ' 
                + CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' months' END
            WHEN sched.freq_type = 64 THEN 'StartUp'
            WHEN sched.freq_type = 128 THEN 'Idle'
          END AS 'frequency'
        , IsNull('Every ' + CAST(sched.freq_subday_interval AS VARCHAR(10)) + 
            CASE WHEN sched.freq_subday_type = 2 THEN ' seconds'
                WHEN sched.freq_subday_type = 4 THEN ' minutes'
                WHEN sched.freq_subday_type = 8 THEN ' hours'
            END, 'Once') AS 'subFrequency'
        , REPLICATE('0', 6 - LEN(sched.active_start_time)) 
            + CAST(sched.active_start_time AS VARCHAR(6)) AS 'startTime'
        , REPLICATE('0', 6 - LEN(sched.active_end_time)) 
            + CAST(sched.active_end_time AS VARCHAR(6)) AS 'endTime'
        , REPLICATE('0', 6 - LEN(jobsched.next_run_time)) 
            + CAST(jobsched.next_run_time AS VARCHAR(6)) AS 'nextRunTime'
        , CAST(jobsched.next_run_date AS CHAR(8)) AS 'nextRunDate'
    FROM msdb.dbo.sysschedules AS sched
    Join msdb.dbo.sysjobschedules AS jobsched
        ON sched.schedule_id = jobsched.schedule_id
    WHERE sched.enabled = 1
)
 
SELECT job.name AS 'jobName'
    , sched.scheduleName
    , sched.frequency
    , sched.subFrequency
    , SUBSTRING(sched.startTime, 1, 2) + ':' 
        + SUBSTRING(sched.startTime, 3, 2) + ' - ' 
        + SUBSTRING(sched.endTime, 1, 2) + ':' 
        + SUBSTRING(sched.endTime, 3, 2) 
        AS 'scheduleTime' -- HH:MM
    , SUBSTRING(sched.nextRunDate, 1, 4) + '/' 
        + SUBSTRING(sched.nextRunDate, 5, 2) + '/' 
        + SUBSTRING(sched.nextRunDate, 7, 2) + ' ' 
        + SUBSTRING(sched.nextRunTime, 1, 2) + ':' 
        + SUBSTRING(sched.nextRunTime, 3, 2) AS 'nextRunDate'
      /* Note: the sysjobschedules table refreshes every 20 min, 
        so nextRunDate may be out of date */
    , 'Execute msdb.dbo.sp_update_job @job_id = ''' 
        + CAST(job.job_id AS CHAR(36)) + ''', @enabled = 0;' AS 'disableScript'
FROM msdb.dbo.sysjobs AS job
Join myCTE AS sched
    ON job.job_id = sched.job_id
WHERE job.enabled = 1 -- do not display disabled jobs
ORDER BY nextRunDate;

Query Test Checklist

• Works on SQL Server 2008: Yes
• Works on SQL Server 2005: Yes
• Works on SQL Server 2000: No
• Works on Standard Edition: Yes
• Works on case-sensitive servers: Yes

Tests Updated by Brent Ozar, 2009-04-01

Order all Job Steps Alphabetically

use msdb

begin try
DECLARE @jobname varchar(128)
--SET @jobname = 'Backup Data'
--SET @jobname = 'Backup Differentials'
--SET @jobname = 'DBCCs'
--SET @jobname = 'Rebuild Indexes'
SET @jobname = 'Update Stats'

begin transaction
	CREATE TABLE #reorder_jobsteps
	(newstepid int identity(1,1),
	jobname varchar(128),
	oldstepid int,
	stepname varchar(128)
	)

	insert #reorder_jobsteps (jobname,oldstepid,stepname)
		select j.name,step_id,step_name from sysjobsteps js inner join sysjobs j on js.job_id=j.job_id
			where j.name = @jobname
			order by step_name

	select * from #reorder_jobsteps

	update js SET step_id = newstepid 
		FROM sysjobsteps js inner join sysjobs j ON js.job_id = j.job_id
		inner join #reorder_jobsteps r ON js.step_name = r.stepname AND j.name = r.jobname
		where j.name = r.jobname and js.step_name = r.stepname

	select j.name,step_id,step_name from sysjobsteps js inner join sysjobs j on js.job_id=j.job_id
		where j.name = @jobname
		order by step_name

	declare @maxstep int
	SELECT @maxstep = max(newstepid) FROM #reorder_jobsteps

	drop table #reorder_jobsteps

	update js SET on_success_action = 3
		from sysjobsteps js INNER JOIN sysjobs j ON js.job_id = j.job_id
		WHERE j.name = @jobname

	update js SET on_success_action = 1
		from sysjobsteps js INNER JOIN sysjobs j ON js.job_id = j.job_id
		WHERE j.name = @jobname AND step_id = @maxstep

	update sysjobs SET start_step_id = 1
		WHERE name = @jobname

	commit transaction
end try
begin catch
	rollback transaction
end catch

Query Test Checklist

• Works on SQL Server 2008: Yes
• Works on SQL Server 2005: Yes
• Works on SQL Server 2000: No
• Works on Standard Edition: Yes
• Works on case-sensitive servers: Yes