June 26, 2008 at 11:20 am
Hi all,
We have implemented a system supporting the sign-off process of deliverables of development projects, which requires sending emails to and fro users all around the world. As these emails may also be triggered by data changes in the database which are difficult to handle otherwise, we chose to use DBMail.
If such a deliverable changes lets say from "under development" to "ready for sign-off" all people who have to sign-off must be notified. This may count up to seven or even more users, there is no built-in restriction. These emails are actually handed over to sp_send_dbmail, as we can see in logs we maintain ourselves. Handing over e.g. seven emails, however, will result in maybe four emails which are actually entered into the queue, sometimes more, sometimes less, although sp_send_mail reports success in every case (return code 0).
We have encapsulated our handling of emails via DBMail in this SP:
[font="Courier New"]CREATE PROCEDURE [dbo].[Mail_send]
@From INT, -- Sender's ID
@To INT, -- Recipient's ID
@Subject NVARCHAR(4000),
@Text NVARCHAR(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @From_LastName NVARCHAR(50)
DECLARE @From_FirstName NVARCHAR(20)
DECLARE @From_Name NVARCHAR(71)
DECLARE @From_Email NVARCHAR(1000)
DECLARE @To_Email NVARCHAR(1000)
DECLARE @r INT
SET @From_Email = NULL
SET @To_Email = NULL
SET @Subject = ISNULL(@Subject, N' ')
SET @Text = ISNULL(@Text, N' ')
-- Step 1: If sender and receiver are the same, nothing will be sent
IF @From = @To
RETURN 0
-- Step 2: Retrieve and set sender details in DBMail profile
IF @From = 0 BEGIN
SET @From_Name = 'Coordinator/Reporter Support'
SET @From_Email = 'coordinator-reporter-support@hisec.at'
END
ELSE BEGIN
SELECT @From_LastName = LastName
, @From_FirstName = FirstName
, @From_Name = FirstName + ' ' + LastName
, @From_Email = Email
FROM User
WHERE UserID = @From
IF @From_Email IS NULL BEGIN
RAISERROR(N'Could not determine email address for user %u (last name: "%s", first name: "%s")', 15, 1,
@From, @From_LastName, @From_FirstName)
RETURN 1
END
END
EXEC @r = msdb.dbo.sysmail_update_account_sp @account_name = 'rzbweb'
, @display_name = @From_Name
, @replyto_address = @From_Email
IF @r <> 0 BEGIN
RAISERROR(N'Could not set sender details', 15, 2)
RETURN 1
END
-- Step 3: Retrieve and set receiver details
SELECT @To_Email = REPLACE(Email, ',', ';') -- Just in case, several email
-- addresses are concatenated
-- by commas
FROM User
WHERE UserID = @To
IF @To_Email IS NULL BEGIN
RAISERROR(N'Could not determine email address for user %u', 15, 3, @To)
RETURN 1
END
-- Step 4: Send mail
SET @Text = @Text + CHAR(10) -- Add a line feed to separate the
-- disclaimer added by SMTP server
EXEC @r = msdb.dbo.sp_send_dbmail @profile_name = 'rzbweb',
@recipients = @To_Email,
@subject = @Subject,
@body = @Text
IF @r <> 0 BEGIN
RAISERROR(N'Could not send email to "%s"', 15, 4, @To_Email)
RETURN 1
END
-- Step 5: Write log
SET @From_Email = ISNULL(@From_Name,'') + ' '
EXECUTE MailProtocol_save @From_Email, @To_Email, @Subject, @Text
RETURN 0
END[/font]
As you can see, the last step is writing to our own log table (SP MailProtocol_save). This step would not be reached if any of the previous steps failed. We do write to the log table, but nothing is added to the mail queue.
Anybody with any ideas?
Best regards,
Dietmar Weickert.
Best regards,
Dietmar Weickert.
June 26, 2008 at 11:35 am
Is it possible that the send-credentials are being messed up by the whole From part? I've used sp_send_dbmail for tons of things, including pretty high traffic stuff, and never had a problem with it, except when credentials were not matching up on the SMTP server.
Also, have you checked the queue in msdb, or just the queue on the SMTP server? Is it getting into msdb and not getting to SMTP, or is it not even getting to msdb?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 27, 2008 at 4:34 am
Hi GSquared,
In the section dealing with the from-address only the displayed name and the reply address are set, the actual sender's address in the profile is not touched at all. This works pretty well with the SMTP server which relays the messages. (We actually had to keep the sender's addess stable as otherwise some addressed servers rejected the messages because of a suspected open relay. The sender's email addresses are not all from our domain. With this solution we were able to send the messages with a trusted technical sender's address that never changes and still provide the real sender's address info in the reply-to field and the displayed user friendly name.)
The messages do not get to the msdb queue, they simply get lost between calling sp_send_dbmail and the queuing process. This is why I suspect a concurrency problem when several messages are sent within a very short timeframe, but so far I could not get any hints that either verify or falsify my assumption.
Best regards,
Dietmar Weickert.
Best regards,
Dietmar Weickert.
June 27, 2008 at 8:29 am
What I'd do next is fire the thing up and run it like 10-thousand times from 10 connections, and log it. Find out what's in common between the ones that don't go out. Just load-test the heck out of it.
The most I've ever run was about 1,000 e-mails per minute, maybe 2,000. Never had a problem with that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 30, 2008 at 8:01 am
Hi GSquared,
Maybe my problem is solved. When I tried to copy a database, the whole system got stuck. Even restarting the SQL Server did not help, so I had to reboot the machine. Since then not only copying of databases works fine again, but we did not lose any more emails as well.
Rather strange for a professional database system... 🙁
Anyway, many thanks for your hints!!!
Best regards,
Dietmar Weickert
Best regards,
Dietmar Weickert.
June 30, 2008 at 9:22 am
Do you have software running on that server (applications or services) other than Windows and SQL Server? Do you have calls to external DLLs, either via sp_OACreateObject, xp_CmdShell, or CLR procs/functions?
The reason I ask is because the usual cause for that behavior is "memory leaks", and they are almost always something outside the SQL Server services. They can do exactly what you're talking about, and they can be "fixed" by rebooting. The problem with that as a solution, is that they often come back, and you end up rebooting every day or something like that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 30, 2008 at 9:36 am
Interesting question!
Yes we do use CLR functions to calculate hash values for password verification. I will keep an eye on it.
Best regards,
Dietmar Weickert.
Best regards,
Dietmar Weickert.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply