Databases

SQL Server: Daily Failed SQL Job Email Report

March 10, 2026 Rich 6 min read

This guide sets up:

  • Database Mail
  • a Database Mail profile
  • a stored procedure that emails failed SQL Agent jobs from the last 24 hours
  • a SQL Agent job scheduled for 11:59 PM daily

What this does

Every night at 11:59 PM, SQL Server Agent runs a stored procedure in msdb that:

  • checks SQL Agent job history for failed jobs in the last 24 hours
  • emails the failed job names, failure time, duration, and failure reason
  • sends a “no failures” email if nothing failed

Prerequisites

You need:

  • SQL Server Agent running
  • an SMTP server you can relay through
  • the following SMTP details:
    • SMTP server name
    • SMTP port
    • whether SSL/TLS is required
    • SMTP username/password if authentication is required
    • From address
    • recipient email address

Step 1: Enable Database Mail XPs

Run this first:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;

Step 2: Create the Database Mail account and profile

Replace the sample values below.

Variables you should replace

  • SQLAlerts = mail profile name
  • SQLAlertsAccount = mail account name
  • smtp.yourdomain.com = SMTP server
  • 587 = SMTP port
  • sqlalerts@yourdomain.com = From address
  • SQL Alerts = display name
  • smtp-user = SMTP username
  • smtp-password = SMTP password

Script

USE msdb;
GO

-- Create Database Mail account
EXEC dbo.sysmail_add_account_sp
    @account_name = N'SQLAlertsAccount',
    @description = N'SQL Server Database Mail account for alerts',
    @email_address = N'sqlalerts@yourdomain.com',
    @display_name = N'SQL Alerts',
    @replyto_address = N'sqlalerts@yourdomain.com',
    @mailserver_name = N'smtp.yourdomain.com',
    @port = 587,
    @enable_ssl = 1,
    @username = N'smtp-user',
    @password = N'smtp-password';
GO

-- Create Database Mail profile
EXEC dbo.sysmail_add_profile_sp
    @profile_name = N'SQLAlerts',
    @description = N'Database Mail profile for SQL alerting';
GO

-- Add account to profile
EXEC dbo.sysmail_add_profileaccount_sp
    @profile_name = N'SQLAlerts',
    @account_name = N'SQLAlertsAccount',
    @sequence_number = 1;
GO

-- Make profile public/default if desired
EXEC dbo.sysmail_add_principalprofile_sp
    @profile_name = N'SQLAlerts',
    @principal_name = N'public',
    @is_default = 1;
GO

Notes

  • If your SMTP server does not require authentication, remove @username and @password.
  • If your SMTP server uses port 25 and no TLS, set:
    • @port = 25
    • @enable_ssl = 0
  • If your mail relay only allows specific source IPs or hosts, make sure the SQL Server can relay through it.

Step 3: Test Database Mail

Run this before doing anything else.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQLAlerts',
    @recipients = 'you@domain.com',
    @subject = 'SQL Database Mail Test',
    @body = 'Database Mail is working.';

If this fails, stop there and fix SMTP/profile/authentication first.

Step 4: Create the stored procedure that emails failed jobs

Replace you@domain.com with the real recipient.

USE msdb;
GO

CREATE OR ALTER PROCEDURE dbo.usp_EmailDailyFailedJobs
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Subject nvarchar(255);
    SET @Subject = N'Daily SQL Job Failures - ' + @@SERVERNAME;

    IF EXISTS
    (
        SELECT 1
        FROM msdb.dbo.sysjobhistory h
        WHERE h.step_id = 0
          AND h.run_status = 0
          AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= DATEADD(DAY, -1, GETDATE())
    )
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'SQLAlerts',
            @recipients   = 'you@domain.com',
            @subject      = @Subject,
            @body         = 'Failed SQL Agent jobs in the last 24 hours:',
            @query = N'
SET NOCOUNT ON;

SELECT
    @@SERVERNAME AS ServerName,
    j.name AS JobName,
    msdb.dbo.agent_datetime(h.run_date, h.run_time) AS FailedAt,
    STUFF(STUFF(RIGHT(''000000'' + CAST(h.run_duration AS varchar(6)), 6), 3, 0, '':''),
          6, 0, '':'') AS RunDuration_HHMMSS,
    h.message AS FailureReason
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j
    ON j.job_id = h.job_id
WHERE
    h.step_id = 0
    AND h.run_status = 0
    AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= DATEADD(DAY, -1, GETDATE())
ORDER BY FailedAt DESC;',
            @execute_query_database = 'msdb',
            @query_result_header = 1,
            @query_result_separator = ' | ',
            @query_result_no_padding = 1;
    END
    ELSE
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'SQLAlerts',
            @recipients   = 'you@domain.com',
            @subject      = @Subject,
            @body         = 'No SQL Agent job failures were recorded in the last 24 hours.';
    END
END
GO

Step 5: Test the stored procedure manually

EXEC msdb.dbo.usp_EmailDailyFailedJobs;

If this does not send mail, do not create the scheduled job yet.

Step 6: Create the SQL Agent job scheduled for 11:59 PM daily

USE msdb;
GO

DECLARE @job_id UNIQUEIDENTIFIER;

-- Create the job
EXEC msdb.dbo.sp_add_job
    @job_name = N'Daily SQL Job Failure Email',
    @enabled = 1,
    @description = N'Sends a daily email at 11:59 PM listing failed SQL Agent jobs from the last 24 hours.',
    @owner_login_name = N'sa',
    @job_id = @job_id OUTPUT;

-- Add the job step
EXEC msdb.dbo.sp_add_jobstep
    @job_id = @job_id,
    @step_name = N'Run Failure Report',
    @subsystem = N'TSQL',
    @database_name = N'msdb',
    @command = N'EXEC msdb.dbo.usp_EmailDailyFailedJobs;',
    @on_success_action = 1,
    @on_fail_action = 2;

-- Create the schedule
EXEC msdb.dbo.sp_add_schedule
    @schedule_name = N'Daily 11:59 PM',
    @enabled = 1,
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = 235900;

-- Attach schedule to job
EXEC msdb.dbo.sp_attach_schedule
    @job_id = @job_id,
    @schedule_name = N'Daily 11:59 PM';

-- Attach job to local server
EXEC msdb.dbo.sp_add_jobserver
    @job_id = @job_id,
    @server_name = N'(LOCAL)';
GO

Step 7: Test the SQL Agent job immediately

Do not wait until midnight to find out it is broken.

EXEC msdb.dbo.sp_start_job
    @job_name = 'Daily SQL Job Failure Email';

Step 8: Check job history

EXEC msdb.dbo.sp_help_jobhistory
    @job_name = 'Daily SQL Job Failure Email';

Helpful verification queries

Show mail profiles

SELECT name, description
FROM msdb.dbo.sysmail_profile;

Show which account is tied to which profile

SELECT 
    p.name AS ProfileName,
    a.name AS AccountName,
    a.email_address,
    a.display_name,
    s.servername,
    s.port
FROM msdb.dbo.sysmail_profile p
JOIN msdb.dbo.sysmail_profileaccount pa
    ON p.profile_id = pa.profile_id
JOIN msdb.dbo.sysmail_account a
    ON pa.account_id = a.account_id
JOIN msdb.dbo.sysmail_server s
    ON a.account_id = s.account_id;

Show public/default profiles

SELECT 
    p.name,
    pp.is_default,
    pp.principal_id
FROM msdb.dbo.sysmail_principalprofile pp
JOIN msdb.dbo.sysmail_profile p
    ON pp.profile_id = p.profile_id;

Show mail log/errors

SELECT *
FROM msdb.dbo.sysmail_event_log
ORDER BY log_date DESC;

Show unsent/sent/failed messages

SELECT *
FROM msdb.dbo.sysmail_allitems
ORDER BY send_request_date DESC;

Common failure points

1. sp_send_dbmail fails

Usually one of these:

  • wrong profile name
  • bad SMTP server/port
  • auth failure
  • relay denied
  • TLS/SSL mismatch
  • firewall blocking outbound SMTP

2. Job never runs

Usually one of these:

  • SQL Server Agent service is stopped
  • schedule was not attached
  • job is disabled

3. Stored procedure compiles but sends nothing

Usually one of these:

  • there were no failed jobs in the last 24 hours
  • recipient email is wrong
  • mail profile is wrong

Blunt recommendation

Build and test in this order:

  1. enable Database Mail XPs
  2. create account/profile
  3. test sp_send_dbmail
  4. create stored procedure
  5. run stored procedure manually
  6. create Agent job/schedule
  7. manually start Agent job
  8. trust the 11:59 PM schedule only after the manual test works

If step 3 fails, everything after it is pointless until SMTP is fixed.

Leave a comment