DB Mail sysmail keeps stopping. sysmail_start_sp takes forever and never finshes

  • i use exec dbo.sysmail_start_sp to start DB Mail but it keeps running forever.

    When i check the status using

    EXEC dbo.sysmail_help_status_sp , it shows Started.

    But i automcatically goes in STOPPED mode and i have to restart DB Mail.

    None of the emails are going through and can be viewed in sysmail_mailitems table with status 0.

    I am running out of ideas.

     

  • Review sysmail_event_log to get additional info on the error(s) that occurred in mail.

    Sometimes you also want to look at sysmail_faileditems but that likely won't be helpful for the type of error you're having here.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • sysmail_event_log says Mail Id 1####has been deleted from sysmail_mailitems table. This mail will not be sent.

    There are about 3Billion records in the table.

    How do i clear this table?

    No records in sysmail_faileditems

  •  

    DECLARE @email_min_date_to_keep datetime;

    /* only delete email older than 7 days; change the -7 to whatever number of days you prefer before running */

    SET @email_min_date_to_keep = DATEADD(DAY, -7, CAST(GETDATE() AS date))

    EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @email_min_date_to_keep

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply