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 msdb 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 #RunningJobsWe 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.
Comments
Post a Comment