Finding SQL Agent Job Failures
Issue
SQL Agent jobs have the ability to notify a user should a failure occur when a job executes. Many times a job may have multiple sequential independent steps, where execution continues to the next step even after one step fails. When the steps within a job are configured this way, the job does not consider itself to have failed, and does not send a notification.
Resolution
The following query provides information about failed jobs and the failing steps. This query can be modified to filter the results further, query based on a time period, or however necessary to suit its particular application. This query could also be used to send Email alerts to a administrator or group of users, build a report, place the contents into another table, or any other useful purpose. For example, a daily scheduled job could be created to check for failures and alert a administrator via email that it requires attention.
SELECT j.name AS jobName
,h.step_id
,h.step_name
,s.command
,s.database_name
,h.sql_message_id as ErrorID
,h.sql_severity as Severity
,ISNULL(msg.description, '') as Description
,h.message
,h.run_date as runDate
,h.run_time as runTime
,h.server
FROM MSDB.dbo.sysjobhistory h WITH (NOLOCK)
JOIN msdb.dbo.sysJobs j WITH (NOLOCK) ON h.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps s WITH (NOLOCK) ON h.step_id = s.step_id
AND h.job_id = s.job_id
LEFT JOIN master.sys.sysmessages msg WITH (NOLOCK)ON h.sql_message_id = msg.error
AND h.sql_severity = msg.severity
AND msg.msglangid = 1033
WHERE sql_severity > 0
The stored procedure sp_send_dbmail could be used to send the raw query results via e-mail message to the specified recipients. Alternatively, additional scripting could be employed to format the body of the email prior to sending.
See RESL203846 for additional information on Configuring SQL Server to Send Emails.