Post reply

xp_sendmail and Agent emails spaced out by 5 minutes ???

  • July 7, 2005 at 5:34 pm

    #363104

    I've got the most bizarre SQL email problem I've ever seen: all emails arriving from my production server are spaced out on arrival by 5 minutes (sometimes 10), no matter if they were originated by xp_sendmail or SQL Agent.

    Email headers do show the correct timestamp for the message origination, but they only arrive to SMTP server every 5 minutes. It's like they are placed into some SQL-specific MAPI queue and being pulled out of there one in a time every 5 (10) minutes. And I'm saying that because tests send from Outlook don't show the same behavior.

    Now, that renders all my monitoring useless. I've searched email-related threads and found the wonderful workaround for MAPI - XPSMTP.DLL from http://sqldev.net

    It takes care of xp_sendmail (and hundreds times faster than "normal" MAPI delivery), but I'm still stuck with an Agent.

    I duplicated the same problem on my standby server, so it's not one-time freak.

    My environment SQL 2000 Enterprise SP4 , Win 2003 Enterprise, Outlook 2000 (9.0.0.2711), Internet email (no Exchange), different email profiles used for Agent and SQL Server

  • July 8, 2005 at 8:03 am

    #572353

    That is strange. When you say they are spaced by 5 minutes, do you mean that the times between all emails are 5 minutes, as in one is sent and then the next doesn't arrive for 5 minutes?

    Or do you mean the diff between the source time and arrive time is 5 minutes later than it should be?

    I haven't seen this type of delay, at least not consistently. I have seen bunches get delayed and then all come at once, but not at a consistent interval. It's possible that some background process is running infrequently, but I'd think it would be around every minute.

  • July 8, 2005 at 11:16 am

    #572532

    I meant that literally - I would receive the message every 5 minutes. The same picture is on the mail server - it gets email from SQL Server every 5 minutes. So when server sends 20 messages, the last one arrives in 100 minutes like a clockwork.

    Since I generated test messages within the seconds (as reflected in headers) the issue must be with MAPI or SQL-MAPI integration.

    I did read multiple Technet articles, and the common thread is that SQL mail works reliably only with Exchange.

    So I wonder if anyone ever found a way to de-couple Agent email from MAPI - it would solve a lot of our problems.

  • July 8, 2005 at 11:28 am

    #572539

    I'm not sure as to the cause of the every 5 minutes, but if you're already using XPSMTP, with a little more work, you can use it in your SQL Server Agent jobs.

    Create two job steps, one for success, one for failure which uses the XPSMTP mechanism to send an email with appropriate subject, etc.. If you're only reporting on failures, obviously you can skill the job step for reporting success. Each job step can branch to another on failure. Point those job steps to the failure email step. If you have a success job step, point the last job step that should run to the one job step that sends out the successful email. And remove the SQLAgentMail stuff.

    K. Brian Kelley
    @kbriankelley

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

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