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
|