/* To use this script with the asynchronous SMTP agent I have described in my sswug.com article "From SQLMail to SMTP in the SQLAgent" Be sure to replace master.dbo.xp_sendmail with sysmon.dbo.safe_sendmail where it occurs in the procedures log_job_error and log_job_completion. lets you keep a history of of Agent job failures and also captures the real message rather than the "Job Failed" message you get from job Notification. Much easier to debug. A procedure for failures only and a procedure for completion success or fail for failure notification: Add a job step at the end of the job that includes a call to log_job_failure Set the step before this step to "end job reporting success" Set any step that should end the job if it fails to go to the failure notification step on failure */ use admin GO CREATE TABLE [dbo].[agent_job_log] ( [id] [int] IDENTITY (1, 1) NOT NULL , [job_name] [sysname] NOT NULL , [step_name] [sysname] NOT NULL , [message] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [RecCreatedDt] [datetime] NULL CONSTRAINT [DF__agent_job__RecCr__69E6AD86] DEFAULT (getdate()), PRIMARY KEY CLUSTERED ( [id] ) ON [PRIMARY] ) ON [PRIMARY] GO use admin GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[log_job_error]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[log_job_error] GO CREATE PROCEDURE [dbo].[log_job_error] @notify_recipient_list varchar(255) AS /******************************************************************************************************* * admin.dbo.log_job_error * Creator: Bill Wunder * home.attbi.com/~bwunder * bwunder@attbi.com * * Date: 3-13-2003 * Outline: Technical details * Procedure will write the most recent failure message saved to msdb.dbo.sysjobhistory * to a logging table. This will allow the message to remain available without causing * job slowness by keeping a large number of historical rows in msdb.dbo.sysjobhistory. * Procedure will also optionally notify any specified persons by email with the job * name and failure message. This will remove the need to use of SQLMail and sysoperator * for job failure notification. * * usage: EXECUTE admin.dbo.log_job_error 'bwunder@attbi.com' * Notes: Set the "On Failure job flow action of all jobs that would be set to "End Job Reporting * to go to a job step that calls this procedure. The @job_name parameter must exactly match * the job name to assure that error messages are properly retrieved from msdb.dbo.sysjophistory * * Modifications * developer name date brief description * ------------------ -------- ------------------------------------------------------------ ********************************************************************************************************/ -- declare variables declare @message_text varchar(1024), -- actually nvarchar in msdb.dbo.sysjobhistory @step_name sysname, @job_name sysname, @subject_line varchar(100) -- set session SET NOCOUNT ON -- create temp table -- body of stored procedure select top 1 @step_name = h.step_name, @message_text = h.message, @job_name = j.name from msdb.dbo.sysjobhistory h inner join msdb.dbo.sysjobs j on h.job_id = j.job_id where substring(APP_NAME(), charindex('0x',APP_NAME()) + 18, 16) = substring(replace(h.job_id, '-',''),17,16) and message not like ('% succeeded%') order by h.run_date desc, h.run_time desc insert admin.dbo.agent_job_log (job_name, step_name, message) values (@job_name, @step_name, @message_text) if @notify_recipient_list is not null begin select @subject_line = 'JOB FAILED: ' + substring(@job_name + ' (step: ' + @step_name + ')',1,87) exec master.dbo.xp_sendmail @recipients = @notify_recipient_list, @subject = @subject_line, @message = @message_text -- raise a level 16 error so the job status will indicate the failure Raiserror ('Job Failed! Notification Sent. See admin.dbo.agent_job_log for additional information',16,1) end RETURN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO use admin GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[log_job_completion]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[log_job_completion] GO CREATE PROCEDURE [dbo].[log_job_completion] @notify_recipient_list varchar(255) AS /******************************************************************************************************* * admin.dbo.log_job_completion * Creator: Bill Wunder * home.attbi.com/~bwunder * bwunder@attbi.com * * Date: 3-13-2003 * Outline: Technical details * Procedure will write the most recent failure message saved to msdb.dbo.sysjobhistory * to a logging table. This will allow the message to remain available without causing * job slowness by keeping a large number of historical rows in msdb.dbo.sysjobhistory. * Procedure will also optionally notify any specified persons by email with the job * name and failure message. This will remove the need to use of SQLMail and sysoperator * for job failure notification. * * usage: EXECUTE admin.dbo.log_job_completion 'bwunder@attbi.com' * Notes: Set the "On Failure job flow action of all jobs that would be set to "End Job Reporting * to go to a job step that calls this procedure. The @job_name parameter must exactly match * the job name to assure that error messages are properly retrieved from msdb.dbo.sysjophistory * * Modifications * developer name date brief description * ------------------ -------- ------------------------------------------------------------ ********************************************************************************************************/ -- declare variables declare @message_text varchar(1024), -- actually nvarchar in msdb.dbo.sysjobhistory @subject_line varchar(100), @step_name sysname, @job_name sysname -- set session SET NOCOUNT ON -- body of stored procedure select top 1 @step_name = h.step_name, @message_text = h.message, @job_name = j.name from msdb.dbo.sysjobhistory h inner join msdb.dbo.sysjobs j on h.job_id = j.job_id where substring(APP_NAME(), charindex('0x',APP_NAME()) + 18, 16) = substring(replace(h.job_id, '-',''),17,16) order by h.run_date desc, h.run_time desc insert admin.dbo.agent_job_log (job_name, step_name, message) values (@job_name, @step_name, @message_text) if (@notify_recipient_list is not null) begin if (@message_text like ('% succeeded%')) begin select @subject_line = 'Job Completed: ' + substring(@job_name,1,87) exec master.dbo.xp_sendmail @recipients = @notify_recipient_list, @subject = @subject_line, @message = @message_text end else begin select @subject_line = 'JOB FAILED: ' + substring(@job_name + ' (step: ' + @step_name + ')',1,87) exec master.dbo.xp_sendmail @recipients = @notify_recipient_list, @subject = @subject_line, @message = @message_text -- raise a level 16 error to assure the job status indicates the failure Raiserror ('Job Failed! Notification Sent. See admin.dbo.agent_job_log for additional information',16,1) end end RETURN GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO use admin GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[purge_agent_job_log]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[purge_agent_job_log] GO create procedure dbo.purge_agent_job_log as /******************************************************************************************************* * dbo.purge_agent_job_log * Creator: Bill Wunder * * usage: EXECUTE admin.dbo.purge_agent_job_log * Notes: * * Modifications * ********************************************************************************************************/ delete admin.dbo.agent_job_log where RecCreatedDt < getdate() - 30 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO