SQL Server 2005 Mail Logs

The new email funcationality in SQL Server 2005 is quite nice. Every time you call msdb.dbo.sp_send_dbmail it sends the email in the background. This is important when you are sending a lot of emails on a already busy server. The natural question then is, how can I see what was sent, and if it was sent properly. The answer is in msdb database.

To see all the email sent out and the contents just do the following:

select * from msdb.dbo.sysmail_mailitems

sent_status field: 0 = waiting to process 1 = success 2 = error with the smtp server

To see if there were any errors, you can check the log using:

select * from msdb.dbo.sysmail_log

If you want to check retries for sending, try this:

select * from msdb.dbo.sysmail_send_retries

There are other tables, but they seem to be managed by the Microsoft SQL Server Management Studio or other stored procedures. Feel free to take a look at them though:

msdb.dbo.sysmail_principalprofile

msdb.dbo.sysmail_profile

msdb.dbo.sysmail_profileaccount

msdb.dbo.sysmail_profile

msdb.dbo.sysmail_profileaccount

msdb.dbo.sysmail_server

msdb.dbo.sysmail_servertype

msdbdbo.sysmail_query_transfer

If you need help setting up SQL mail in the first place, follow the link above.