Find erratic jobs
-
Is there any way to find which jobs failed during the previous day with T-SQL?
Prakash
Kindest Regards,R
-
in sql 2000 you can find the job histories & outcomes
in msdb.dbo.sysjobhistory
-
Maybe you'll find some script a the SSC-scripts section.
Here's one I install by default for dev-support :
-- it uses the msdb-tables and will need to be checked every release
use master
go
CREATE PROC sp_ALZDBA_check_failed_jobs
@NumDays int = 1,
@RecentOnly char(1) = 'Y',
@Job_Name varchar(132) = Null
AS
-- example: sp_ALZ_check_failed_jobs 5, 'N','_TransferSFA'
SET NOCOUNT ON
PRINT 'Checking for all jobs that have failed in the last ' + CAST(@NumDays AS char(2)) +' days.......'
PRINT ' '
if @Job_Name is null
begin
if @RecentOnly = 'Y'
begin
select ErrJobs.*
from (
SELECT
convert(datetime,substring(cast(run_date as char(8)) ,1,4) + '-' + substring(cast(run_date as char(8)) ,5,2) + '-' + substring(cast(run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,5,2)
 
AS Failure_DateTime,T2.name AS Job_Name,
T1.step_id AS Step_Nr,
T1.step_name AS Step_Name,
T1.message AS Message
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)
 
ErrJobsinner join
( SELECT max(
convert(datetime,substring(cast(run_date as char(8)) ,1,4) + '-' + substring(cast(run_date as char(8)) ,5,2) + '-' + substring(cast(run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,5,2)
 
) AS Max_Failure_DateTime,T2.name AS Job_Name,
T1.step_id AS Step_Nr,
T1.step_name AS Step_Name
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)
Group by T2.name, T1.step_id , T1.step_name
 
MaxErrJobson ErrJobs.Failure_DateTime = MaxErrJobs.Max_Failure_DateTime
and ErrJobs.Job_Name = MaxErrJobs.Job_Name
and ErrJobs.Step_Nr = MaxErrJobs.Step_Nr
and ErrJobs.Step_Name = MaxErrJobs.Step_Name
order by ErrJobs.Failure_DateTime desc
end
else --RecentOnly <> 'Y'
begin
SELECT
convert(datetime,substring(cast(run_date as char(8)) ,1,4) + '-' + substring(cast(run_date as char(8)) ,5,2) + '-' + substring(cast(run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,5,2)
 
AS Failure_DateTime,T2.name AS Job_Name,
T1.step_id AS Step_Nr,
T1.step_name AS Step_Name,
T1.message AS Message
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)
order by Failure_DateTime desc
end
end
else -- @Job_Name is ingevuld
begin
if @RecentOnly = 'Y'
begin
select ErrJobs.*
from (
SELECT
convert(datetime,substring(cast(run_date as char(8)) ,1,4) + '-' + substring(cast(run_date as char(8)) ,5,2) + '-' + substring(cast(run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,5,2)
 
AS Failure_DateTime,T2.name AS Job_Name,
T1.step_id AS Step_Nr,
T1.step_name AS Step_Name,
T1.message AS Message
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)
AND T2.name = @Job_Name
 
ErrJobsinner join
( SELECT max(
convert(datetime,substring(cast(run_date as char(8)) ,1,4) + '-' + substring(cast(run_date as char(8)) ,5,2) + '-' + substring(cast(run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,5,2)
 
) AS Max_Failure_DateTime,T2.name AS Job_Name,
T1.step_id AS Step_Nr,
T1.step_name AS Step_Name
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)
AND T2.name = @Job_Name
Group by T2.name, T1.step_id , T1.step_name
 
MaxErrJobson ErrJobs.Failure_DateTime = MaxErrJobs.Max_Failure_DateTime
and ErrJobs.Job_Name = MaxErrJobs.Job_Name
and ErrJobs.Step_Nr = MaxErrJobs.Step_Nr
and ErrJobs.Step_Name = MaxErrJobs.Step_Name
order by ErrJobs.Failure_DateTime desc
end
else
begin
SELECT
convert(datetime,substring(cast(run_date as char(8)) ,1,4) + '-' + substring(cast(run_date as char(8)) ,5,2) + '-' + substring(cast(run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(run_time + 1000000,6) as char(6)) ,5,2)
 
AS Failure_DateTime,T2.name AS Job_Name,
T1.step_id AS Step_Nr,
T1.step_name AS Step_Name,
T1.message AS Message
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)
AND T2.name = @Job_Name
order by Failure_DateTime desc
end
end
GO
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I use this:
select b.server , a.name, b.step_name,b.sql_message_id, b.sql_severity
from msdb.dbo.sysjobs a, msdb.dbo.sysjobhistory b
where a.job_id = b.job_id
and (b.run_date = convert(int,convert(varchar,getdate(),112)) or
b.run_date + 1 = convert(int,convert(varchar,getdate(),112)))
and b.run_status in (0,3)
-
If it's just for yesterday, then why not opt for using EM ? It's quick and easy !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply