March 16, 2015 at 5:42 am
Hi All,
I have created SQL JOB and configured Db mail also for respective job. My requirement is that , if the job is failed or success I should get notification mail with reason, why job has failed. There is so many job has created so I can't go and check each job history. I have setup job and I am getting notification like below:
JOB RUN:'Mail on login failed : State 5' was run on 3/16/2015 at 2:41:03 PM
DURATION:0 hours, 0 minutes, 0 seconds
STATUS: Failed
MESSAGES:The job failed. The Job was invoked by User . The last step to run was step 1 (Step 1 - send e-mail).
But As per the above notification I could not find the reason behind the failure so I can go check job history and find the reason. Instead of checking job history I want to display error message here itsself.
Can anyone please help me to resolve this issue.
Thanks in advance.
March 17, 2015 at 7:30 am
Your configuration means you are getting an alert sent to the operator configured for the job.
From your description what you really want is an email that summarises the failure.
I have a SSIS package that does something similar which loops over all our servers, and sends a summary email of all job failures that have not subsequently been successful looking back over X hours.
I can't share the package as it has some sensitive data in it but I have included some of the code below to help you along:
Create this procedure on your sever you want to report from in a suitable database:
CREATE PROCEDURE usp_report_failed_jobs @CheckOverLastXHours int AS
BEGIN
-- Build list of all succeeded jobs
WITH CTE_MostRecentSuccess AS (
select j.name AS JobName,
MAX(msdb.dbo.agent_datetime(run_date,run_time) )AS [Scheduled Run Date]
FROM msdb..sysjobs j
JOIN msdb..sysjobhistory h on j.job_id = h.job_id
WHERE j.enabled = 1
AND h.run_status = 1
AND h.step_name = '(Job outcome)'
AND h.run_time >= 1000
AND msdb.dbo.agent_datetime(run_date,run_time) > DATEADD(hh,-@CheckOverLastXHours,getdate())
GROUP BY j.name),
---- Build list of all failed job steps
CTE_Currentfailures AS (
select j.name AS JobName,
MIN(CASE WHEN step_name = '(Job outcome)' THEN [message] END) AS [Step Failure Message],
MAX(msdb.dbo.agent_datetime(run_date,run_time) )AS [Scheduled Run Date],
ROW_NUMBER() OVER (PARTITION BY j.name ORDER BY run_date DESC, run_time DESC) RankNo
FROM msdb..sysjobs j
JOIN msdb..sysjobhistory h on j.job_id = h.job_id
WHERE j.enabled = 1
AND h.run_status <> 1
AND h.step_name = '(Job outcome)'
AND msdb.dbo.agent_datetime(run_date,run_time) > DATEADD(hh,-@CheckOverLastXHours,getdate())
GROUP BY j.name, h.run_date, h.run_time)
-- Report a List of jobs which latest status is a failure in the last 8 hours.
SELECTCAST(@@ServerName AS NVARCHAR(100)) AS Server,
CAST(f.JobName AS NVARCHAR(1000)) AS [Job Name],
CAST(f.[Step Failure Message] AS NVARCHAR(4000)) AS [Step Failure Message] ,
f.[Scheduled Run Date]
FROM CTE_Currentfailures f
LEFT JOIN CTE_MostRecentSuccess s on f.JobName = s.JobName
WHERE f.RankNo = 1
AND f.[Scheduled Run Date] > ISNULL(s.[Scheduled Run Date],'1980-01-01')
AND f.[Scheduled Run Date] > DATEADD(hh,-@CheckOverLastXHours,getdate())
END
Then call the procedure and send the results via an email with HTML format this code gives you a basis for the email sending:
SET NOCOUNT ON
--Declare variables for use througout the code.
DECLARE @EmailRecipient VARCHAR(1000)
DECLARE @emailSub NVARCHAR(256)
DECLARE @ProfileName VARCHAR(1000)
DECLARE @jobHTML VARCHAR(MAX)
DECLARE @status INT
DECLARE @LastXHours INT
DECLARE @Environment NVARCHAR(100)
DECLARE @ServerAdminGroup NVARCHAR(100)
-- SET Variable Values
SET @EmailRecipient = 'test@yourcompanycom'
SET @LastXHours = 1
SET @Environment = 'PRODUCTION'
/**********************************************************************/
/*!!!!!!!!!!!!!!!! DO NOT MODIFY BELOW THIS LINE !!!!!!!!!!!!!!!!!!!!!*/
/**********************************************************************/
SELECT TOP 1 @ProfileName = name FROM msdb.dbo.sysmail_profile
-- Remove Failed Jobs from the list if they have an exception
DELETE FROM tmp_sql_failedjobs
WHERE EXISTS (
SELECT *
FROM sql_failedjobs_exceptions E
WHERE E.Server = tmp_sql_failedjobs.Server
AND E.JobName = tmp_sql_failedjobs.JobName
)
-- if all previous top X excecutions of the job are over the threshold
-- start formatting the email.
SET @status =CASE WHEN (SELECT COUNT(1) FROM tmp_sql_failedjobs) > 0 THEN 1
ELSE 2
END
SET @emailSub = CASEWHEN @status = 1 AND @Environment = 'PRODUCTION' THEN '!!! PRODUCTION Job Failures Detected !!!'
WHEN @status = 1 AND @Environment <> 'PRODUCTION' THEN '!!! NON PRODUCTION Job Failures Detected !!!'
END
--Set the html text for the jobs and or package failures.
SET @JobHTML =
N'<H3 style="color:red; font-family:verdana">Job failures in the last '
+ CONVERT(VARCHAR(10),@LastXHours) + ' hours have been detected which have no subsequent successful completion, see details below' +
'</H3>' +
N'<p align="left" style="font-family:verdana; font-size:8pt">
' +
N'<table border="3" style="font-size:8pt; font-family:verdana; text-align:left">' +
N'<tr style="color:42426F; font-weight:bold"><th>Admin Group</th><th>Environment</th><th>Server</th><th>Job Name</th><th>Step failure Message</th><th>Execution Date</th></tr>' +
CAST((
SELECT
td = ServerAdminGroup, '',
td = EnvType, '',
td = Server, '',
td = JobName, '',
td = StepFailureMessage, '',
td = ScheduledRunDate
FROM tmp_sql_failedjobs
ORDER BY Server, JobName
FOR XML PATH('tr'), TYPE
) AS VARCHAR(MAX) ) +
N'</table>'
IF EXISTS (SELECT TOP 1 1 FROM tmp_sql_logprogress WHERE JobCollectionSucceeded = 0 OR ConnectionAvailable = 0)
BEGIN
SELECT @jobHTML = @jobHTML + N'<p style="color:blue; font-family:verdana">The following servers encountered an error when performing failed job collection ''' +
N'<p align="left" style="font-family:verdana; font-size:8pt">
' +
N'<table border="3" style="font-size:8pt; font-family:verdana; text-align:left">' +
N'<tr style="color:42426F; font-weight:bold"><th>Type</th><th>Category</th><th>Server</th><th>Connect Success</th><th>Collect Success</th><th>ErrorDetails</th></tr>' +
CAST((
SELECT
td = ServerAdminGroup, '',
td = EnvType, '',
td = Server, '',
td = CASE WHEN ConnectionAvailable = 1 THEN 'Y' ELSE 'N' END, '',
td = CASE WHEN JobCollectionSucceeded = 1 THEN 'Y' ELSE 'N' END, '',
td = ISNULL(ErrorDetails,'N/A'),''
FROM tmp_sql_logprogress
WHERE ConnectionAvailable = 0 OR JobCollectionSucceeded = 0
ORDER BY ServerAdminGroup, EnvType, Server
FOR XML PATH('tr'), TYPE
) AS VARCHAR(MAX) ) +
N'</table>'
END
IF @status = 1
BEGIN
--Send the status report email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients=@EmailRecipient,
@body=@jobHTML,
@body_format = 'HTML',
@subject =@emailSub
END
SELECT @JobHTML
This is not a complete solution, but gives you a start.
MCITP SQL 2005, MCSA SQL 2012
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply