SQL query to show SQL server job failure error message details

The automatic job failure notification doesn’t show the error details.

So one of the work around is to have a step with following T-SQL and to start/execute  it on the job step failure:

declare @body1 nvarchar(MAX)
declare @subject1 nvarchar(MAX)

set @body1 = (select top 1 message from msdb..sysjobhistory a
inner join msdb..sysjobs b on a.job_id = b.job_id
where b.name =’Job Name ABC’ AND step_id = 1
order by a.instance_id desc)

set @body1 = ‘On Server :’+@@servername + ‘ : ‘ + @body1

set @subject1 = ‘On Server :’+@@servername + ‘ Job failed: Job Name ABC ‘

EXEC msdb.dbo.sp_send_dbmail @recipients=’abc@xyz.com’,
@profile_name = ‘EMAIL PROFILE NAME’,
@subject = @subject1,
@body = @body1,
@body_format = ‘HTML’ ;

Reference 1


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s