January 28, 2014 at 8:21 am
I have a mutli step sql server job that when one of the steps fail an alert needs to be sent out, however the job can't fail on this step it needs to continue. I have tried a trigger on sysjobshistory but that has gotten kind of ugly. So for example
3 step job
step 1 completes goes to step 2
step 2 fails (need alert to go out now) continues to step3
step 3 succeeds job is complete
I need the alert to go out when step 2 fails, not at job completion, some of these steps take quite a while
January 28, 2014 at 8:33 am
You could create an alert based on the error number you recieve when it fails. Then set the step to continue to the next step on failure.
January 28, 2014 at 9:11 am
the problem is that these steps are either large sp's or SSIS error will not always be the same
January 28, 2014 at 9:36 am
I always do this by having a failure step for every actual step that simply sends an email and continues to the next actual step. Very easy to do with sp_send_dbmail.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 28, 2014 at 9:54 am
What about creating a step 4 in the original job that sends an email using sp_send_dbmail. Then if step 2 fails it jumps to job 4 sends an email then jumps to job 3 to complete the process.
Send DBMail
January 28, 2014 at 9:57 am
Duh never thought of simple, I did get a nice trigger that works but your way would have been faster. My trigger is at least flexible enough that it will work on any failure of a step for any job
January 28, 2014 at 12:38 pm
I run a job every hour that queries the system tables looking for job step failures. If it finds some within "x" time frame, it sends me an email.
Pro: It finds every failed step and no special handling needs to be done in the job. So any new jobs and steps are automatically included in alerts.
Con: I only get alerts when my scheduled "Alert" job runs, not when the actual job fails. In my case, it's ok for me to get notified a bit later than the actual event. Time sensitive jobs might need more immediate notice.
declare @RecCount int
DECLARE @tableHTML NVARCHAR(MAX) ;
declare @SubjectText varchar(150)
declare @BodyText varchar(150)
set @SubjectText = 'Failed Job Steps - Server A ' + CONVERT(varchar(32), GETDATE(), 101) + ' @ ' + CONVERT(varchar(32), GETDATE(), 108)
set @BodyText = ' '
set @RecCount = 0
-- Get count of failed steps for specified time period
set @RecCount = (SELECT count(*)
FROM [msdb].[dbo].[sysjobhistory] h
join msdb..sysjobs j on j.job_id = h.job_id
where msdb.dbo.agent_datetime(h.run_date,h.run_time) > getdate() -1
and run_status <> 1 -- not succeeded
and h.step_id > 0)-- only look at actual steps
-- At least 1 failed step, so send email with query results showing job step info.
if @RecCount > 0 begin
SET @tableHTML = @BodyText +
N'<H1>Failed Job Steps</H1>' +
N'<table border="1">' +
N'<tr><th>Job Name</th><th>Step#</th><th>Step Name</th>' +
N'<th>Run Date</th><th>Server</th></tr>' +
CAST ( ( select td = j.name ,'', td = cast(step_id as char(7)), ' ',
td = step_name , '',
td = DATENAME(dw, ((msdb.dbo.agent_datetime(run_date, run_time))) )+', '+ cast((msdb.dbo.agent_datetime(run_date, run_time)) as char(23)), ' ' ,
td = [server] , ' '
FROM [msdb].[dbo].[sysjobhistory] h
join msdb..sysjobs j on j.job_id = h.job_id
where msdb.dbo.agent_datetime(h.run_date,h.run_time) > getdate() -1
and run_status <> 1 -- not succeeded
and h.step_id > 0-- only look at actual steps, not overall job
order by run_date desc, run_time desc, j.name, step_id
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBA_Alerts',
@recipients= 'Me@MyCompany.com',
@subject = @SubjectText,
@body = @tableHTML,
@body_format = 'HTML',
@importance = 'High';
end
This works on SQL 2005 & 2008 r0. I have not tried it on other versions.
January 28, 2014 at 5:57 pm
Check the following article:
Logging and Error Handling for SQL Stored Procedures [/url]
In the TRY steps, where it captures the error information, you can add code to send your email alert.
You would also need to switch off the bits that skip steps after an error occurs.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 17, 2019 at 8:12 pm
Try this posting. I'm using the script from Mamta Kaushik (4th from the top) as a final step in my jobs.... It gives you the step that failed, and the failure reason in an email sent to whatever operator you specify. It's old but works....running on SQL 2014 Enterprise with SP2.
https://stackoverflow.com/questions/3858472/notify-operator-if-any-step-in-job-fails
My only complaint and there is a fix I'm sure is it does take into account retries (place to alter the number you have your steps set to) but it will notify you even if the retry succeeds. Not sure now to tell it NOT to notify me if the retry is successful but say the 1st run fails. The same post has a short script that does that but I like how this formats the email WITH the error message included.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply