One great way to keep track of failed jobs is to setup email notifications. In SQL 2000 the email system was called SQL Mail. In SQL 2005 it is called Database Mail and is very different. Here are some instructions to setup Database Mail and some nifty queries to manage it.

Setup Database Mail:
1. In Management Studio expand the Management folder.
2. Right click on Database Mail, Configure Database Mail.
3. From the main menu you can add and edit profiles and accounts.
4. For a new system select the top option of ‘Set up Database Mail by performing the following tasks:’
5. Click Yes to enable the mail feature.
6. Fill in the appropriate fields with your mail info, choose defaults for the rest and make your way to the finish page.
7. Now right click on SQL server Agent, Properties, Alert System.
8. Check the box ‘Enable mail profile’. Your mail profile name should show up.
9. Restart the SQL Agent Service from within windows.
10. You can send a test by right clicking Database Mail, Send Test E-mail.

Setup an Operator:
1. Under SQL Server Agent right click Operators, New Operator.
2. Pick a name, and fill in the E-mail name (address).

Setup Job Failure Notifications:
1. Now under Jobs, select a job and right click properites, Notifications.
2. Check the E-mail box, select your Operator from the drop down, and select ‘When the job fails’.

Now your job will notify you via email if it fails. But what’s happening behind the scenes with the mail system? Here are some queries that let you see what’s going on.

USE msdb
GO

-- Show Mail Event Log
SELECT event_type AS [Type], log_date AS LogDate, [Description]
FROM sysmail_event_log
ORDER BY log_date DESC

-- Show Sent Messages
SELECT recipients AS [To], [Subject], Body, sent_status AS [Status], sent_date AS DateSent
FROM sysmail_sentitems
ORDER BY sent_date DESC

-- Show Failed Messages
SELECT recipients AS [To], [Subject], Body, sent_status AS [Status], sent_date AS DateSent
FROM sysmail_faileditems
ORDER BY sent_date DESC

-- Show All Items
SELECT recipients AS [To], [Subject], Body, sent_status AS [Status], sent_date AS DateSent
FROM sysmail_allitems
ORDER BY sent_date DESC

-- Show Unsent Messages
SELECT recipients AS [To], [Subject], Body, sent_status AS [Status], sent_date AS DateSent
FROM sysmail_unsentitems
ORDER BY sent_date DESC

And there you have it. You can now setup email notifications and see what the email system is doing.