As we were talking about to get start time and end time of schedule jobs a week before, We are going to use same information, but it will be with the scheduled jobs having last status is filed for any steps. So I request to go through that post. Also visit post to get running jobs and the way to get it. The DBA needs to know the scheduled job status on routine duty.
Some of the jobs having schedule occurrence one time and some of them have recursive occurrence. Need to list out those jobs having last status is Fail. It may be possible some jobs have multiple steps and among them some fail, so those jobs should be coming into the list. And have to exclude running jobs, even if are failed earlier.
Here I am sharing one script which will alert us with those jobs which failed today and have following criteria,
Some of the jobs having schedule occurrence one time and some of them have recursive occurrence. Need to list out those jobs having last status is Fail. It may be possible some jobs have multiple steps and among them some fail, so those jobs should be coming into the list. And have to exclude running jobs, even if are failed earlier.
Here I am sharing one script which will alert us with those jobs which failed today and have following criteria,
- List out failed jobs for today
- List out jobs which have a last status as fail
- Need Job Name, Start Time and End Time too
- Excluding running jobs
USE msdbWe can also set an alert for those failed jobs in email. I would like you to share your thoughts and the way you are using to get failed job details.
GO
-- Creating a temp table
CREATE TABLE #RunningJobs
(
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT 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
)
-- Insert the running jobs in this temp table
INSERT INTO #RunningJobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'sa'
SELECT
SERVERPROPERTY ('servername') as ServerName,
res.name as JobName,
-- converting run_date and run_time to proper date time format for Start Time
CAST(
CONVERT(CHAR(8), run_date, 112) + ' ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':')
AS DateTime) AS StartedAt,
-- converting run_date,run_time and run_duration to proper date time format for Finish Time
(CONVERT (VARCHAR,
(DATEADD(ss,
(CASE LEN(run_duration)
WHEN 1
THEN run_duration
WHEN 2
THEN run_duration
WHEN 3
THEN (CAST(Left(right(run_duration,3),1) as int)*60)
+ (right(run_duration,2))
WHEN 4
THEN (CAST(Left(right(run_duration,4),2) AS int)*60)
+ (right(run_duration,2))
WHEN 5
THEN (CAST(Left(right(run_duration,5),1) AS int)*3600)
+ (CAST(Left(right(run_duration,4),2) AS int)*60)
+ right(run_duration,2)
WHEN 6
THEN (CAST(Left(right(run_duration,6),2) AS int)*3600)
+ (CAST(Left(right(run_duration,4),2) AS int)*60)
+ right(run_duration,2)
END ),
CAST(
CONVERT(CHAR(8), run_date, 112) + ' ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':')
AS DateTime)))
,120)) AS FailedAt
FROM (SELECT Row_number()
OVER(
partition BY sj.name
ORDER BY run_time DESC) AS rnk,
sj.job_id,
sj.name,
run_date,
run_time,
run_duration,
run_status
FROM msdb.dbo.sysjobhistory sjh WITH (nolock)
INNER JOIN msdb.dbo.sysjobs sj WITH (nolock)
ON sjh.job_id = sj.job_id
WHERE sjh.step_id <> 0
-- considering for today only
AND run_date = CONVERT(VARCHAR(8), Getdate(), 112)
) res
WHERE
-- Failed status
res.run_status = 0
-- Lastly failed
AND res.rnk = 1
-- excluding running jobs
AND NOT EXISTS (SELECT rj.job_id
FROM #runningjobs rj
WHERE rj.job_id = res.job_id
AND rj.running = 1)
-- Droping table
DROP TABLE #RunningJobs
