October 20, 2008 at 4:31 am
Hi
I am having some problems identifying which jobs has failed in the last hour on SQL Server 2000.
On SQL Server 2005 I use this:
SELECT DISTINCT GETDATE() as ts, name, start_execution_date, stop_execution_date, message, server
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobactivity ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobactivity.job_id
INNER JOIN msdb.dbo.sysjobhistory ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobhistory.job_id
WHERE (stop_execution_date > dateadd(hh, -1, getdate()) OR start_execution_date > dateadd(hh, -1, getdate()))
AND message LIKE 'The job failed%'
To identify which jobs has failed in the last day on SQL Server 2000 I found this:
SELECT sjh.server,sj.name, CONVERT(VARCHAR(30),sjh.message) as message , sjh.run_date, sjh.run_time
FROM msdb..sysjobhistory sjh
JOIN msdb..sysjobs sj on sjh.job_id = sj.job_id
JOIN (SELECT job_id, max(instance_id) maxinstanceid
FROM msdb..sysjobhistory
WHERE run_status NOT IN (1,4)
GROUP BY job_id) a ON sjh.job_id = a.job_id AND sjh.instance_id = a.maxinstanceid
WHERE DATEDIFF(dd,CONVERT(VARCHAR(8),sjh.run_date), GETDATE()) <= 1
I can't seem to make it return the failed jobs in the last hour though :unsure:
Can anyone help me figure out how to retrieve that?
October 20, 2008 at 10:00 am
Try changing WHERE DATEDIFF(dd,CONVERT(VARCHAR(8),sjh.run_date), GETDATE()) <= 1
to
WHERE DATEDIFF(hh,CONVERT(VARCHAR(8),sjh.run_date), GETDATE()) <= 1
Greg
October 21, 2008 at 2:44 am
Greg Charles (10/20/2008)
Try changing WHERE DATEDIFF(dd,CONVERT(VARCHAR(8),sjh.run_date), GETDATE()) <= 1to
WHERE DATEDIFF(hh,CONVERT(VARCHAR(8),sjh.run_date), GETDATE()) <= 1
Hi Greg
Thanks for the suggestion - I tried it myself with no success though.
I have come up with my own solution to the problem:
As run_date and run_time in msdb..sysjobhistory both are int values, instead of datetime (As start_execution_date in SQL Server 2005) making me able to compare them with the present date and time, I converted the two int's into one datetime.
That enables me to compare the values, and establish whether or not a job has failed during the last hour.
Here's my code:
-- converts the run_date and run_time int's into a datetime
DECLARE @date char(8)
DECLARE @time_hh char(2)
DECLARE @time_mm char(2)
DECLARE @time_ss char(2)
SET @date = CONVERT(char(8), (SELECT TOP 1 run_date FROM msdb..sysjobhistory WHERE run_status = 0 ORDER BY instance_id DESC))
SET @time_hh = (SELECT TOP 1 SUBSTRING(CONVERT(char(8), run_time),1,2) FROM msdb..sysjobhistory WHERE run_status = 0 ORDER BY instance_id DESC)
SET @time_mm = (SELECT TOP 1 SUBSTRING(CONVERT(char(8), run_time),3,2) FROM msdb..sysjobhistory WHERE run_status = 0 ORDER BY instance_id DESC)
SET @time_ss = (SELECT TOP 1 SUBSTRING(CONVERT(char(8), run_time),5,2) FROM msdb..sysjobhistory WHERE run_status = 0 ORDER BY instance_id DESC)
DECLARE @datetime_char datetime
SET @datetime_char = CONVERT(datetime, (SELECT @date + ' ' + @time_hh + ':' + @time_mm + ':' + @time_ss))
-- checks whether or not the last job to fail was executed less than an hour ago
IF (@datetime_char > dateadd(hh, -1, getdate()))
BEGIN
PRINT 'One or more jobs failed in the past hour'
-- insert what to do when jobs have failed
END
ELSE
BEGIN
PRINT 'No jobs failed in the last hour'
-- insert what to do when no jobs has failed
END
Of course the action taken when a job has failed, shouldn't be a print - it's just to see if it works 🙂
If anyone has comments/suggestions to the code I would appreciate it.
September 11, 2009 at 2:00 am
This is what I use to list failed jobs
SELECT DISTINCT
CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) AS 'Failure Date',
SUBSTRING(T2.name,1,40) AS 'Job Name',
T1.step_id AS 'Step_id',
T1.step_name AS 'Step Name',
LEFT(T1.[message],500) AS 'Error Message'
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
WHERE T1.run_status NOT IN (1,4)
AND T1.step_id != 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112)
November 23, 2009 at 11:13 am
Knight (9/11/2009)
This is what I use to list failed jobsSELECT DISTINCT
CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) AS 'Failure Date',
SUBSTRING(T2.name,1,40) AS 'Job Name',
T1.step_id AS 'Step_id',
T1.step_name AS 'Step Name',
LEFT(T1.[message],500) AS 'Error Message'
FROM msdb..sysjobhistory T1
JOIN msdb..sysjobs T2
ON T1.job_id = T2.job_id
WHERE T1.run_status NOT IN (1,4)
AND T1.step_id != 0
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112)
i'm still a bit rough on t-sql
can you tell me what the 112 is for at the very end?
November 24, 2009 at 5:26 am
i'm still a bit rough on t-sql
can you tell me what the 112 is for at the very end?
Hi
The 112 at the end is a format style for dates. 112 is the ISO standard -> yymmdd
You can see several ways of formatting dates here: http://www.sql-server-helper.com/tips/date-formats.aspx
February 17, 2010 at 5:15 am
THis will give the list of the jobs even there any step also gets failed.
select distinct b.server, a.name 'Job_Name' ,b.step_id ,
+' Jobs run_status :'+ convert(char(2), b.run_status) +' ,' + b.message as message,
B.run_date,B.run_time, B.run_duration
From msdb.dbo.sysjobs a
join msdb.dbo.sysjobhistory b on a.job_id = b.job_id
join msdb.dbo.SysJOBSteps D on a.job_id = D.job_id
where b.run_status in(1,0)
and D.last_run_date <> 0
and D.last_run_time <> 0
and (message like '%(10) without succeeding%' or message like '%failed%')
and Message not like '%The step succeeded%'
and b.instance_id in ( select Max(instance_id) from msdb.dbo.sysjobhistory C where C.job_id=A.job_id GROUP BY Run_date, step_id )
and b.run_date >= convert(char(10),getdate()-1,112)
August 3, 2010 at 4:03 am
Hi
I Use this in a scheduled job to run every hour to check for failed jobs.
I also set up a db mail procedure to email if any jobs fail on that server.
Saves setting up notification for all the jobs when one procedure covers them all.
---------------------------------------------------------------------------
SELECT distinct servername = sjh.server
, job_name = sj.name
, step_name = sjh.step_name
, left(cast(sjh.run_date as char(10)),4)
+ '-' + substring(cast(sjh.run_date as char(10)),5,2)
+ '-' + substring(cast(sjh.run_date as char(10)),7,2)
+ ' ' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),sjh.run_time), 6), 1, 2)
+ ':' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), sjh.run_time), 6) ,3 ,2)
+ ':' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),sjh.run_time), 6) ,5 ,2) as [Start_Time]
FROM msdb.dbo.sysjobhistory sjh, msdb.dbo.sysjobs sj
WHERE sj.job_id = sjh.job_id
and sjh.step_id = 0
and sjh.run_status = 0
and left(cast(sjh.run_date as char(10)),4)
+ '-' + substring(cast(sjh.run_date as char(10)),5,2)
+ '-' + substring(cast(sjh.run_date as char(10)),7,2)
+ ' ' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),sjh.run_time), 6), 1, 2)
+ ':' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), sjh.run_time), 6) ,3 ,2)
+ ':' + substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),sjh.run_time), 6) ,5 ,2) >= CONVERT(char(19), (select dateadd (hour,(-1), getdate())), 121)
--------------------------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply