Troubleshooting MSSQL 2005 Database Mail

Check the Logs

First things first - view the database mail logs. Right click on Database Mail, and select View Database Mail Log. You are looking for clues here. See any? Nope, OK, lets look at some more logs.

-- list log events
SELECT * FROM msdb.dbo.sysmail_log
GO


-- list error messages
SELECT * FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id >= 1

Anything? Hmm - OK, let's look at the mail. There are several tables which contain mail items. Just think about the difference between Failed items and Unsent items for a minute.

--To list all items:
USE msdb
SELECT sent_status, *
FROM sysmail_allitems
GO


--To list the unsent items only:
USE msdb
SELECT sent_status, *
FROM sysmail_unsentitems
GO


--To list the failed items only:
USE msdb
SELECT sent_status, *
FROM sysmail_faileditems
GO

Via.