xp_sendmail hanging

  • Hi,

    We have had a couple of instances on our server where all our jobs have got stuck trying to send emails overnight. We have tried to do the xp_stopmail however this returns an error saying that it failed to stop the process. We have also tried stopping the SQL Agent Service however this takes far too long to come back up and eventually we have to reboot the server.

    I've had a look through the Event Viewer on the server and cannot see any errors and the server goes back to normal once it comes back up, we are using xp_sendmail in each of the jobs and I was wondering if anyone knew what could be causing this or could point me in the right direction to find out whats happening. There tends to be a gap of a couple of months between instances and I cannot see any errors on our Exchange server.

    Thanks in advance.

  • Just brainstorming I wonder if it could be an Outlook issue as it's obviously not permissions or access if it usually runs. I found the following thread: http://www.sqlservercentral.com/Forums/Topic436438-146-1.aspx

    The user has found an alternative procedure 'xp_SMTP_sendmail' - unfortunately the link doesn't seem to work but perhaps you can find it elsewhere.

    Typing 'xp_sendmail outlook hanging' into Google shows some similar issues, apparently Outlook can create dialog boxes (that aren't visible) making the process hang.

    Otherwise, what do your mail profiles look like, and what parameters are you using for xp_sendmail? What are these jobs doing?

    BOL says the following (https://technet.microsoft.com/en-us/library/aa260697(v=sql.80).aspx

    If query is specified, xp_sendmail logs in to SQL Server as a client and executes the specified query. SQL Mail makes a separate connection to SQL Server; it does not share the same connection as the original client connection issuing xp_sendmail.

    Note query can be blocked by a lock held by the client connection issued xp_sendmail. For example, if you are updating a table within a transaction and you create a trigger for update that attempts to select the same updated row information as the query parameter, the SQL Mail connection is blocked by the exclusive lock held on row by the initial client connection.

    Do you have the latest service pack installed?

    Sorry can't be more help, but will hopefully aid direction to a solution!

  • Thanks tindog, i've had a quick look for xp_SMTP_sendmail but its looking increasingly likely that this could be the way to go.

    The jobs are mainly just sending the results of SQL Queries to different people around the business or they are notifying people that processes have been completed overnight by the database.

    If I'm honest there are a lot of Windows updates that need to be applied on the server but it is in constant use 7 days a week so its difficult to find a window to do a restart. I was wondering if there could be a bug fix we need to apply.

    I'll try and find the download for xp_SMTP_sendmail and go from there.

    Thanks for your response, any help is appreciated.

Viewing 3 posts - 1 through 2 (of 2 total)

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