January 19, 2009 at 11:37 am
Hi All,
I have Few Applications jobs (Production)which has multiple steps and Passes to the next step on failure(Business requirement).And not sending any notification to DBA Group.
But the issue i have is my monitering scripts, doesn't detect this an an failure and doesn't notify me.(Though the status of the job says as Warning, but actually its a failure).
Can anyone help on this, so that i get the failure notification message:
sysjobhistory has only these many options available
run_status when 0 then 'Failed' when 1 then 'Successful' when 3 then 'Cancelled' when 4 then 'In Progress'
Many Thanks in Advance.
January 19, 2009 at 11:58 am
The following is a job script i created to monitor job failures. You can modify it by replacing the email address.
USE [msdb]
GO
/****** Object: Job [Job Failure alert] Script Date: 01/06/2009 08:20:54 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 01/06/2009 08:20:54 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Job Failure alert',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'check every 30 minutes, whenever there is a failure in the jobs, an email will be sent.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Job Failure alert] Script Date: 01/06/2009 08:20:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Job Failure alert',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'
DECLARE @body varchar(max)
DECLARE @subject varchar(500)
SELECT @subject='' Job: <''
+ j.[name] +
''> failed! '' , @body='' Job: ''
+ j.[name] +
''>-->> Step '' +
convert(char(1),jh.step_id) +
'': ''+ Step_name + ''> failed on ''
--Convert Integer date to regular datetime
+ LEFT(RIGHT(''000000'' + CAST(run_time AS VARCHAR(10)),6),2) + '':'' +
SUBSTRING(RIGHT(''000000'' + CAST(run_time AS VARCHAR(10)),6),3,2) + '':'' +
RIGHT(RIGHT(''000000'' + CAST(run_time AS VARCHAR(10)),6),2) + '', ''+ SUBSTRING(CAST(jh.run_date AS CHAR(8)),5,2) + ''/'' +
RIGHT(CAST(jh.run_date AS CHAR(8)),2) + ''/'' +
LEFT(CAST(jh.run_date AS CHAR(8)),4)
--Change run time into something you can reecognize (hh:mm:ss)
--Change run duration into something you caan recognize (hh:mm:ss)
+ '' after running for ''+ LEFT(RIGHT(''000000'' + CAST(run_duration AS VARCHAR(10)),6),2) + '':'' +
SUBSTRING(RIGHT(''000000'' + CAST(run_duration AS VARCHAR(10)),6),3,2) + '':'' +
RIGHT(RIGHT(''000000'' + CAST(run_duration AS VARCHAR(10)),6),2) +''[HH:MM:SS]. ''
from msdb.dbo.sysjobhistory jh join msdb..sysjobs j on j.job_id=jh.job_id
where run_status =''0'' and step_id>0
and datediff(mi, convert (datetime, convert(varchar(9),run_date),112)
+
convert (datetime,left ((right (''0'' + convert (varchar(9), jh.run_time),6)),2)+'':''+right(left ((right
(''0'' + convert (varchar(9), jh.run_time),6)),4),2)+'':''+right(((right (''0'' + convert (varchar(9), jh.run_time),6))),2),8) , getdate())<30
order by instance_id
IF @subject IS NOT NUll
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients =N''YOUREMAIL ADDRESS''
,@body = @body
,@body_format =''HTML''
,@subject =@subject
END',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'every 30 minutes',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=30,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081203,
@active_end_date=99991231,
@active_start_time=500,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
January 20, 2009 at 11:54 am
Hello sunny,
Thanks for the Post. But would it return a Warning for the Job which fails.?:)
January 20, 2009 at 12:07 pm
Yes, it will.
The job is scheduled to run every 30 minutes (u can modify it to run as frequently as you want by changing the 30 in the WHERE clause). It will check whether there is any job failure in the past 30 minutes. If there is any, it will send u an email with subject as ' ServerName-->>JobName-->>JobStep FAILED' and will attach the error message in the email body.
Try to create a testing job with syntex error... for example, 'select mytestjob';
then run the testing job, it should fail; and then run the monitoring job. it should send an email to u.
January 21, 2009 at 10:47 am
Sunny,
Thanks a Lot for this code and think this post has also helped many.
I will try this out.
Thanks once again.:)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply