Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

SQL Server lets you configure a job to send email alerts when it fails. This is a simple and effective way to monitor your jobs. However, these alerts do not include any detail--just a success or failure notice.

If a job fails, this is what a typical alert email will look like:

JOB RUN:        'DBA - Consistency Check Databases' was run on 8/14/2011 at 12:00:04 AM
DURATION:       0 hours, 0 minutes, 0 seconds
STATUS:         Failed
MESSAGES:       The job failed.  The Job was invoked by Schedule 2 (Nightly Before 
                Backup 12AM).  The last step to run was step 1 (Check Databases).

To determine the cause of the failure, you have to navigate to the instance in SQL Server Management Studio, find the job, and view its execution history. In a large environment it can be a pain to have to constantly do this.

The ideal alert email would include the failure reason upfront and let you get straight to working on the solution.

I am familiar with this solution to this problem. Does anyone have any experience with it? Its drawbacks are:

  1. you have to add a new step every job you have, and
  2. you have to pray that no-one messes up the alert proc, spDBA_job_notification

Has anyone come up with a better solution?

share|improve this question

3 Answers 3

up vote 8 down vote accepted

Something you might do that is just a thought, throwing ideas out...

Create a single job that periodically checks the job table in msdb to see if any jobs show as failed, that can be done with a good T-SQL query. Then you could go into the sysjobsteps table and see if an output log is set for the job. Have a stored procedure send an email attaching that file to it. You would be able to see exactly what the job did from beginning to failure without having to touch the server.

Then could also have PowerShell script check the event log for errors. It allows you to filter down a pretty good bit to get exactly what message types you are looking for. You could set that up as a SQL Agent job to run periodically. Then in the PowerShell script use the email cmdlet to send the message if it finds one.

Far fetched ideas here, just some I thought about.

share|improve this answer

Give this a try and just plug in your variables as needed in TSQL. The key here is to put this as the very last step of each individual SQL agent job but each job step above it needs to go to NEXT STEP whether it is FAILURE or SUCCESS... Works for me just fine for the most part but please do report any issues you run across. We're on SQL Server 2008 R2 so this is where it's used where I've set it up currently.

SELECT  step_name, message
FROM    msdb.dbo.sysjobhistory
WHERE   instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
                                WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
        AND job_id = $(ESCAPE_SQUOTE(JOBID))
        AND run_status <> 1 -- success

IF      @@ROWCOUNT <> 0
        RAISERROR('*** SQL Agent Job Prior Step Failure Occurred ***', 16, 1)

DECLARE @job_name NVARCHAR(256) = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = $(ESCAPE_SQUOTE(JOBID)))
DECLARE @email_profile NVARCHAR(256) = 'SQLServer Alerts'
DECLARE @emailrecipients NVARCHAR(500) = ''
DECLARE @subject NVARCHAR(MAX) = 'SQL Server Agent Job Failure Report: ' + @@SERVERNAME
DECLARE @msgbodynontable NVARCHAR(MAX) = 'SQL Server Agent Job Failure Report For: "' + @job_name + '"'

--Dump report data to a temp table to be put into XML formatted HTML table to email out
SELECT sjh.[server]
INTO #TempJobFailRpt
FROM msdb..sysjobhistory sjh
INNER JOIN msdb..sysjobs sj ON (sj.job_id = sjh.job_id)
WHERE run_date = convert(INT, convert(VARCHAR(8), getdate(), 112))
    AND run_status != 4 -- Do not show status of 4 meaning in progress steps
    AND run_status != 1 -- Do not show status of 1 meaning success
    AND NAME = @job_name
ORDER BY run_date

        SELECT *
        FROM #TempJobFailRpt

-----Build report to HTML formatted email using FOR XML PATH
    <H1>' + @msgbodynontable + '</H1>
        <table border="1" style=
        "background-color: #C0C0C0; border-collapse: collapse">
        <caption style="font-weight: bold">
            Failure occurred in the SQL Agent job named: ''' + @job_name + ''' in at least one of the steps. 
            Below is the job failure history detail for ALL runs of this job today without needing to connect to SSMS to check.

    <th style="width:25%; text-decoration: underline">SQL Instance</th>
    <th style="text-decoration: underline">Job Name</th>
    <th style="text-decoration: underline">Step</th>
    <th style="text-decoration: underline">Message Text</th>
    <th style="text-decoration: underline">Job Run Date</th>
    <th style="text-decoration: underline">Job Run Time</th>
</tr>' + CAST((
            SELECT td = [server]
                ,td = NAME
                ,td = step_id
                ,td = [message]
                ,td = run_date
                ,td = run_time
            FROM #TempJobFailRpt a
            ORDER BY run_date
            FOR XML PATH('tr')
                ,ELEMENTS XSINIL
            ) AS NVARCHAR(MAX)) + '

EXEC msdb.dbo.sp_send_dbmail @profile_name = @email_profile
    ,@recipients = @emailrecipients
    ,@subject = @subject
    ,@body = @tableHTML
    ,@body_format = 'HTML'

--Drop Temp table
    DROP TABLE #TempJobFailRpt
    PRINT '*** No Records Generated ***' 
    DROP TABLE #TempJobFailRpt
share|improve this answer

I have experience with the aforementioned idea. It's good, but a better idea would be to do something like Shawn said.

What we did was to make a job that runs every 5 minutes and scans MSDB tables about job failures. For each job that had a failure we'd run the SP spDBA_job_notification with it's own ID, so the SP will scan the MSDB history steps for errors and email them all. From the SP documentation: "The stored procedure uses the job ID to query the msdb agent tables for the most recent error message for that job."

So instead of just changing every job, better create a single one that does all ;-).

Another idea is to set all jobs to write to Windows Event Viewer in case of errors/failures and read from there with extended proc xp_ReadErrorLog or an automatic tool, if you have that already in your network. For example we used HPOV to check any system issues and could configure a simple alert for all event viewer errors (no need for any custom job or procedure).

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.