March 23, 2009 at 3:59 pm
I am pretty new to SQL Server and can't seem to find a way to include more details about a job failure in the email notification. Ideally, I would like to see in the email the same message I see in history for the job (for failures only since I only get emails on failures).
The current email structure I get follows.
JOB RUN:'REMOTE_FTEA_DTS_METRICS' was run on 3/23/2009 at 8:00:00 AM
DURATION:0 hours, 5 minutes, 31 seconds
STATUS: Failed
MESSAGES:The job failed. The Job was invoked by Schedule 7 (REMOTE_FTEA_DTS_METRICS). The last step to run was step 1 (EXEC FTEA_DTS_METRICS).
I would like to see reason for failure in addition to the above. Is this something I can configure?
Thanks,
Thomas
March 23, 2009 at 10:59 pm
When you look at the job’s history, you can check the checkbox that shows the job’s steps. This will give you a bit more information about the error. You can also try to run the job that fails from SSMS (don’t use sp_start_job. Instead run the code that is used by the job). This will show you all error messages on the screen.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 24, 2009 at 6:06 am
This has driven me insane. This is not available and I have no idea why this would not be functionality, it's not there in 2008 either (however on the feature request list for the next major release.
The best way is to setup a trigger on sysjobhistory and have that send a notification when there is an error. Here's an example for one that I use on 2008.
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* drop trigger trg_stepfailures */
CREATE trigger [dbo].[trg_stepfailures]
on [dbo].[sysjobhistory]
for insert
as
declare @strcmd varchar(800),@strRecipient varchar(500),@strMsg varchar(2000), @strServer varchar(255),@strTo varchar(255)
Declare @Subject varchar(500)
if exists (select * from inserted where run_status = 0 and step_name NOT IN ( '(job outcome'))
begin
select @strMsg = @@servername + '-Job: ' + sysjobs.name + '. Step= ' +
inserted.step_name +
'Message ' + inserted.message
from inserted
join sysjobs
on inserted.job_id = sysjobs.job_id
where inserted.run_status = 0
--subject text
select @subject = 'Job ' + sysjobs.name + ' Failed on Job Server' +@@Servername
from inserted
join sysjobs
on inserted.job_id = sysjobs.job_id
where inserted.run_status = 0
--server text
--Select @strServer =@@servername+'yourdomain.com'
--recipeints text
SET @strRecipient= 'your.recipients@yourdomain.com'
exec msdb.dbo.sp_send_dbmail @profile_name = null --uses the default profile
, @recipients = @strRecipient
, @subject = @subject
, @body = @strMsg
, @body_format = 'HTML' --default is TEXT
end
March 24, 2009 at 7:32 am
The above code worked perfectly. I changed '(job outcome' to '(Job outcome)' but other than that, this meets the need exactly!
Thanks!
March 24, 2009 at 7:41 am
As another alternative (if you are the anti-trigger type) SQL Magazine had a good article a few years back to get this information in a pretty convenient way.
http://www.sqlmag.com/Article/ArticleID/96056/sql_server_96056.html
If you do not have a SQL Magazing subscription you can google spDBA_job_notification and get the same information. It works nicely but is certainly no better or worse than the above alternative.
March 24, 2009 at 7:44 am
thomas_w_rawley (3/24/2009)
The above code worked perfectly. I changed '(job outcome' to '(Job outcome)' but other than that, this meets the need exactly!Thanks!
Yeah, sorry, was trying to change the code I had as anything with a font tag gets removed, and I also have various jobs that I don't want to report failures on with this process and was removing those.
March 25, 2009 at 8:31 am
Nicholas,
I stole your strigger. Thanks.
I did put your name in the source so I can remember where I got it from.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply