September 6, 2008 at 9:08 pm
Can you please tell me which stored procedure in MSDB provide last Job transaction failed or success with error description if failed. Thanks.
September 6, 2008 at 10:15 pm
try msdb.dbo.sp_help_job
---------------------------------------
elsasoft.org
September 6, 2008 at 11:33 pm
No, it doesn't give error description, if job failed. I tried sp_help_history procedure. But it is giving Job details but not error description. I need to see error description also . Plase let me know.
Thanks.
September 7, 2008 at 3:41 am
Maybe this one can help you:
CREATE PROC spc_ALZDBA_check_failed_jobs
@NumDays int = 1,
@RecentOnly char(1) = 'n',
@Job_Name varchar(132) = Null
AS
begin
-- example: sp_ALZDBA_check_failed_jobs 5, 'N','MEStoDB2DTM_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)
) ErrJobs
inner 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
) MaxErrJobs
on 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
) ErrJobs
inner 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
) MaxErrJobs
on 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
end
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
September 9, 2008 at 8:59 am
It is working but it is giving error message for successed job also.
September 10, 2008 at 5:20 am
Most of my jobs only have step with "Fail job if step fails", so I've only checked for the failed steps.
This version only reports if the actual job failed (including the failed step).
ALTER PROC spc_ALZDBA_check_failed_jobs
@NumDays int = 1,
@RecentOnly char(1) = 'Y',
@Job_Name varchar(132) = Null
AS
begin
-- example: sp_ALZDBA_check_failed_jobs 5, 'N','MEStoDB2DTM_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(T1.run_date as char(8)) ,1,4) + '-' + substring(cast(T1.run_date as char(8)) ,5,2) + '-' + substring(cast(T1.run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(T1.run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(T1.run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(T1.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
INNER JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
INNER JOIN msdb..sysjobhistory T0
ON T0.job_id = T2.job_id
AND T0.STEP_ID = 0
and T0.run_status = 0
and T0.run_date = T1.run_date
and T0.run_time = T1.run_time
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND T1.run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)
) ErrJobs
inner join
( SELECT max(
convert(datetime,substring(cast(T1.run_date as char(8)) ,1,4) + '-' + substring(cast(T1.run_date as char(8)) ,5,2) + '-' + substring(cast(T1.run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(T1.run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(T1.run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(T1.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
INNER 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
) MaxErrJobs
on 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(T1.run_date as char(8)) ,1,4) + '-' + substring(cast(T1.run_date as char(8)) ,5,2) + '-' + substring(cast(T1.run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(T1.run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(T1.run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(T1.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
INNER JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
INNER JOIN msdb..sysjobhistory T0
ON T0.job_id = T2.job_id
AND T0.STEP_ID = 0
and T0.run_status = 0
and T0.run_date = T1.run_date
and T0.run_time = T1.run_time
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND T1.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(T1.run_date as char(8)) ,1,4) + '-' + substring(cast(T1.run_date as char(8)) ,5,2) + '-' + substring(cast(T1.run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(T1.run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(T1.run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(T1.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
INNER JOIN msdb..sysjobhistory T0
ON T0.job_id = T2.job_id
AND T0.STEP_ID = 0
and T0.run_status = 0
and T0.run_date = T1.run_date
and T0.run_time = T1.run_time
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND T1.run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)
AND T2.name = @Job_Name
) ErrJobs
inner join
( SELECT max(
convert(datetime,substring(cast(T1.run_date as char(8)) ,1,4) + '-' + substring(cast(T1.run_date as char(8)) ,5,2) + '-' + substring(cast(T1.run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(T1.run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(T1.run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(T1.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 T1.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
) MaxErrJobs
on 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(T1.run_date as char(8)) ,1,4) + '-' + substring(cast(T1.run_date as char(8)) ,5,2) + '-' + substring(cast(T1.run_date as char(8)) ,7,2)
+ ' ' +
substring(cast(RIGHT(T1.run_time + 1000000,6) as char(6)) ,1,2) + ':' + substring(cast(RIGHT(T1.run_time + 1000000,6) as char(6)) ,3,2) + ':' + substring(cast(RIGHT(T1.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
INNER JOIN msdb..sysjobhistory T0
ON T0.job_id = T2.job_id
AND T0.STEP_ID = 0
and T0.run_status = 0
and T0.run_date = T1.run_date
and T0.run_time = T1.run_time
WHERE T1.run_status <> 1
AND T1.step_id > 0
AND T1.run_date >= CONVERT(char(8), (select dateadd (day,(-1*@NumDays), getdate())), 112)
AND T2.name = @Job_Name
order by Failure_DateTime desc
end
end
end
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
September 10, 2008 at 4:26 pm
Thanks for sharing your code. I wrote little differently to get result for my reqirement.
-------------------------------------------------------------
CREATE PROCEDURE [dbo].[spGetJobDetails] (@Job_Name as Varchar(50))
AS
SET NOCOUNT ON
SELECT TOP 2 sj.name,CASE WHEN run_status=1 THEN 'SUCCESS' ELSE 'FAILED' END, message,server,
CONVERT(VARCHAR(10), CONVERT(DATETIME, CONVERT(VARCHAR(8), sjh.run_date)), 101) as run_date,
substring(right('00000' + convert(varchar(6), sjh.run_time), 6), 1, 2) + ':' + substring(right('00000' + convert(varchar(6), sjh.run_time), 6), 3, 2) + ':' + substring(right('00000'
+ convert(varchar(6), sjh.run_time), 6), 5, 2) as run_time,
substring(right('00000' + convert(varchar(6), sjh.run_duration), 6), 1, 2) + ':' + substring(right('00000' + convert(varchar(6), sjh.run_duration), 6), 3, 2) + ':' + substring(right('00000'
+ convert(varchar(6), sjh.run_duration), 6), 5, 2) as run_duration
FROM sysjobhistory sjh INNER JOIN sysjobs sj
ON sj.job_id=sjh.Job_id
WHERE name=@Job_Name and sj.enabled=1
and CONVERT(VARCHAR(10), CONVERT(DATETIME, CONVERT(VARCHAR(8), sjh.run_date)), 101) = convert(varchar(10),getdate(),101)
And step_id = 1
Order by run_date, run_time desc
-----------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply