SQL Server 2005 Database mail Issue

Question

SQL Server 2005 Database mail Issue

Rookee DBA

SSCrazy

Points: 2188
More actions
November 4, 2008 at 4:01 am

#154880

We are facing some issue with Sql Server 2005 Database Mail Queue functionality using "msdb.dbo.sp_send_dbmail" system stored procedure. After some time email functionality goes into suspended mode. SQL server executes a SP named "msdb.dbo.sp_readrequest;" which goes into suspended mode. This happen most of the time.
As per MSDN help; this sp executes after some interval for checking queue data; for sending/receiving email information. And as per MSDN link "http://support.microsoft.com/kb/931175" the code mentioned in the sp_readrequest sp also incorrect.
"The incorrect code causes the sp_readrequest stored procedure to end without executing the END CONVERSATION statement. Therefore, SQL Server 2005 cannot process queued messages and cannot write the Database Mail log."
As per above link , Microsoft has suggested to do change in the system sp "sp_readrequest", I did the same thing but still its not working. Again this "sp_readrequest" sp gets into suspended mode when it is internally executed by Sql Server.
There is nothing being logged in sql logs or event viewer. Can any one help in understanding how to track what is going wrong and where?
Cheers
Siddarth

Viewing 9 posts - 1 through 8 (of 8 total)

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

krayknot SSCertifiable Points: 7998 More actions · Answer 1

siddartha pal (11/4/2008)
We are facing some issue with Sql Server 2005 Database Mail Queue functionality using "msdb.dbo.sp_send_dbmail" system stored procedure. After some time email functionality goes into suspended mode. SQL server executes a SP named "msdb.dbo.sp_readrequest;" which goes into suspended mode. This happen most of the time.
As per MSDN help; this sp executes after some interval for checking queue data; for sending/receiving email information. And as per MSDN link "http://support.microsoft.com/kb/931175" the code mentioned in the sp_readrequest sp also incorrect.
"The incorrect code causes the sp_readrequest stored procedure to end without executing the END CONVERSATION statement. Therefore, SQL Server 2005 cannot process queued messages and cannot write the Database Mail log."
As per above link , Microsoft has suggested to do change in the system sp "sp_readrequest", I did the same thing but still its not working. Again this "sp_readrequest" sp gets into suspended mode when it is internally executed by Sql Server.
There is nothing being logged in sql logs or event viewer. Can any one help in understanding how to track what is going wrong and where?
Cheers
Siddarth

Execute this sp "msdb.dbo.sp_send_dbmail" after closing all the connectioins with dummy values to ensure whether there is any problem with the server. if it works, restart the server and carry on

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Rookee DBA SSCrazy Points: 2188 More actions · Answer 2

We have checked the same and looks that some issues related to implementation of the same. I dont think that Re-start is the solution to the problem :). We cant re-start SQL server for Production environment.

Cheers

Siddarth

Rookee DBA SSCrazy Points: 2188 More actions · Answer 3

Just would like to give more information about the issue…whenever we are calling sp_send_dbmail for sending an email with attachment, under our application transaction….sometimes msdb database moves into suspect mode. And the table named "sysmail_attachments_transfer" gets locked while inserting data into sysmail_attachments_transfer table by sp_send_dbmail sp.

We are generating 3 word document at runtime using SQL Clr functionality; and generated file we are sending to users using sp_send_dbmail systems stored procedure.

Whenever this sp gets executed sql server creates another db session which is executing sp_readrequest sp; if this db session is working fine then msdb is working fine. And if session of execution of sp_readrequest goes into suspended mode and CAMS database is trying to execute again "sp_send_dbmail" sp; database server creates another db session for msdb which goes into suspended mode with the command "(@P1 nchar(72),@P2 nvarchar(90),@P3 int,@P4 image)INSERT sysmail_attachments_transfer(uid, filename, filesize, attachment) VALUES((@P1), (@P2), (@P3), (@P4))"

This happenes only while sending 3 times email with attachment to user in a single transaction.

Here we are using while loop for sending email to user based on the conditions).

i_amhers Mr or Mrs. 500 Points: 554 More actions · Answer 4

Hello,

I doubt the original posters are looking at this as a "problem" but I didn't see the answer really provided. I thought the following link might assist anyone in the future if they found there way here: http://vendoran.spaces.live.com/Blog/cns!A1AA2222A8B0305C!311.entry.

Thanks,

Jeremy

Shellz SSC Journeyman Points: 77 More actions · Answer 5

Shellz

SSC Journeyman

Points: 77

December 17, 2010 at 10:11 am

#1264154

This answers it!

Carlo-403060 SSC Enthusiast Points: 155 More actions · Answer 6

I had the same problem and switched over to "xp_smtp_sendmail" - this also works on SQL2008 etc.

You just need to get the DLL + script to create the extended SP.

John D Right there with Babe Points: 787 More actions · Answer 7

Hi,

I am seeing this same problem on 2005. Does anyone know if the blog http://vendoran.spaces.live.com/Blog/cns!A1AA2222A8B0305C!311 was transferred to another site?

Thanks for the help.

Vendoran SSC Veteran Points: 217 More actions · Answer 8

You can find it here now - http://www.aaronlowe.net/archive/2007/09/sp_readrequest/

Thanks and hopefully it's useful.

Aaron Lowe

@Vendoran