none
Unstoppable Database Mail

    Question

  • Hello,

    I setup an alert system using Database Mail on SQL Server 2008 Std Windows 8

    After a couple of successful tests, I started to receive the same email thousands of times. I could not stop it; stopped the queue, removed the account and profile, stop sql service. Eventually killing the Message Queuing processes seemed to help then enabling Service Broker started it again.

    At this point, I am not sure what caused it, even where to start. Any idea?

    The following is the code I use to send out emails.

    while exists(select * from EmailsToGo WHERE alert = 0)     
    begin       
    SELECT TOP 1 @EmailID = EmailID,@Recipients = Recipients,             
    @Subject = Subject, @Body = Body 
    FROM EmailsToGo          
    exec msdb.dbo.sp_send_dbmail @recipients = @Recipients, @subject = @Subject, @body = @Body         
    UPDATE EmailsToGo SET alert = 1 where EmailID = @EmailID     
    END

    while exists(select * from EmailsToGo WHERE alert = 0)     begin         select top 1 @EmailID = EmailID,@Recipients = Recipients,             @Subject = Subject, @Body = Body from EmailsToGo          exec msdb.dbo.sp_send_dbmail @recipients = @Recipients, @subject = @Subject, @body = @Body         UPDATE EmailsToGo SET alert = 1 where EmailID = @EmailID     END
    I feel like something messed up in msdb or Message Queuing service.


    • Edited by KuzeyI Monday, September 30, 2013 9:36 PM update
    Monday, September 30, 2013 9:33 PM

Answers

  • Hello,

    A few different things:

    1. The main issue is in the body of the while loop you don't have a where clause so it will constantly grab the very top item and only send that item. You can fix this by adding "WHERE alert = 0"

    2. The second issue is that the return value from the send_dbmail procedure is never checked. This means the mail could have failed but will still be marked as sent in your table. Assuming it will always be sent is a bad assumption.

    3. The table EmailsToGo hopefully has some type of pruning or archiving function to take old alerts out. If not, the table could become large, and without something like a filtered index on alert = 0, SQL Server may switch to a table scan instead of a seek (depends) which could bring down performance (as well as bloat the data cache, etc).

    -Sean


    Sean Gallardy | Blog | Twitter

    • Marked as answer by KuzeyI Monday, September 30, 2013 10:26 PM
    Monday, September 30, 2013 10:19 PM

All replies

  • Hello,

    A few different things:

    1. The main issue is in the body of the while loop you don't have a where clause so it will constantly grab the very top item and only send that item. You can fix this by adding "WHERE alert = 0"

    2. The second issue is that the return value from the send_dbmail procedure is never checked. This means the mail could have failed but will still be marked as sent in your table. Assuming it will always be sent is a bad assumption.

    3. The table EmailsToGo hopefully has some type of pruning or archiving function to take old alerts out. If not, the table could become large, and without something like a filtered index on alert = 0, SQL Server may switch to a table scan instead of a seek (depends) which could bring down performance (as well as bloat the data cache, etc).

    -Sean


    Sean Gallardy | Blog | Twitter

    • Marked as answer by KuzeyI Monday, September 30, 2013 10:26 PM
    Monday, September 30, 2013 10:19 PM
  • Fixed.

    Thank you

    Monday, September 30, 2013 10:27 PM