SQL Server 2005 Database Mail Failures

Question

I am running database mail on a SQL 2005 box. Occasionally mails fail to send, by quering the msdb.dbo.sysmail_mailitems table i can see there are items with a sent_status of "2", which is failed. I can query the sysmail_faileditems table to list all failed mails.

Is there anyway i can process/re-send these failed mail's?

Would it be reasonable to create a daily job to query this table looping through using a CURSOR to re-send the mails one by one, and then delete them from the table one by one.

If you have a better suggestion / ideas then please let me know.

Many thanks Karl

Answer 1

First up, i suggest you query faileditems to determine your main cause of failure:

SELECT  items.subject ,
        items.last_mod_date ,
        l.description
FROM    dbo.sysmail_faileditems AS items
        INNER JOIN dbo.sysmail_event_log AS l ON items.mailitem_id = l.mailitem_id

If it's nothing that can be easily fixed, you can re-send them by looping through the sysmail_mailitems table and re-sending them based on the failure type (timeouts etc) in the faileditems log - some good examples in the suggestions of this blog: http://justgeeks.blogspot.co.uk/2007/05/resending-sysmail-emails.html

My personal favourite:

CREATE PROCEDURE sysmail_resend_timeout
AS 
    BEGIN
        SET NOCOUNT ON

        DECLARE SYSMAIL_LOG_RESEND_CURSOR CURSOR READ_ONLY
        FOR
            SELECT DISTINCT
                    l.mailitem_id ,
                    p.name ,
                    m.recipients ,
                    m.subject ,
                    m.body_format ,
                    m.body
            FROM    msdb.dbo.sysmail_log l WITH ( NOLOCK )
                    JOIN msdb.dbo.sysmail_mailitems m WITH ( NOLOCK ) ON m.mailitem_id = l.mailitem_id
                    JOIN msdb.dbo.sysmail_profile p WITH ( NOLOCK ) ON p.profile_id = m.profile_id
            WHERE   l.event_type = 3
                    AND m.sent_status = 2
                    AND l.description LIKE '%The operation has timed out%'
            ORDER BY l.mailitem_id

        OPEN SYSMAIL_LOG_RESEND_CURSOR

        WHILE ( 1 = 1 ) 
            BEGIN
                DECLARE @mailitem_id INT ,
                    @profile_name NVARCHAR(128) ,
                    @recipients VARCHAR(MAX) ,
                    @subject NVARCHAR(255) ,
                    @body_format VARCHAR(20) ,
                    @body NVARCHAR(MAX)
                FETCH NEXT FROM SYSMAIL_LOG_RESEND_CURSOR INTO @mailitem_id, @profile_name, @recipients, @subject, @body_format, @body
                IF NOT @@FETCH_STATUS = 0 
                    BEGIN
                        BREAK
                    END

                PRINT CONVERT(VARCHAR, GETDATE(), 121) + CHAR(9) + CONVERT(VARCHAR, @mailitem_id) + CHAR(9) + @recipients

                EXEC msdb.dbo.sp_send_dbmail 
                    @profile_name = @profile_name ,
                    @recipients = @recipients ,
                    @subject = @subject ,
                    @body_format = @body_format ,
                    @body = @body

                UPDATE  msdb.dbo.sysmail_mailitems
                SET     sent_status = 3
                WHERE   mailitem_id = @mailitem_id

            END

        CLOSE SYSMAIL_LOG_RESEND_CURSOR

        DEALLOCATE SYSMAIL_LOG_RESEND_CURSOR

    END
GO 

Answer 2

I know it's not really the answer you want to hear, but I always try and decouple the mail feature. I might use a trigger to spawn an external process if the mail sending needs to be timely, but I let the external script do the actual job of sending the mail. That way transient connection errors are taken care of by the MTA, and I don't have to worry about special book-keeping algorithms.