one dbmail is getting delivered thousands of time

Question

one dbmail is getting delivered thousands of time

skb 44459

SSCarpal Tunnel

Points: 4768
More actions
July 25, 2022 at 11:33 am

#4067812

One of the dbmail is getting delivered thousands of time. Same recipient, same subject , same body.
I know its happening from dbmail. When i do select count(*) from sysmail_mailitems. I see number increasing.
Any idea why this is happening. I am sure that there is no process which is attempting sending multiple times.

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

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

Johan Bijnens SSC Guru Points: 135373 More actions · Answer 1

SELECT M.mailitem_id
     , M.profile_id
     , M.recipients
     , M.subject
 --    , M.body
 --    , M.body_format
     , M.send_request_date
     , M.send_request_user
     , M.sent_account_id
     , M.sent_status
     , M.sent_date
     , M.last_mod_date
     , M.last_mod_user
FROM sysmail_allitems M
WHERE sent_date > DATEADD(dd, -7, GETDATE())
ORDER BY sent_date DESC;

This will give you an idea which account is sending the emails using which profile.

Maybe that gets you started.

Extended events may also help you out !

CREATE EVENT SESSION [DBA_Track_sp_send_dbmail] ON SERVER 
ADD EVENT sqlserver.module_start
(
    SET collect_statement=1
    ACTION
    (
        sqlserver.client_app_name,
        sqlserver.database_name,  
        sqlserver.session_server_principal_name,
        sqlserver.username,                     
        sqlserver.sql_text,
        sqlserver.tsql_stack
    )
    WHERE 
    (
        [object_type]='P ' /* The space behind P is necesssary */        AND [object_name]=N'sp_send_dbmail'
    )
)

(added)

Did you check the content of sysmail_faileditems and sysmail_event_log in msdb ?

This reply was modified 2 years, 4 months ago by Johan Bijnens.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 2

skb 44459 wrote:

I am sure that there is no process which is attempting sending multiple times.

Don't be so sure. We had a similar problem with a "wonderful" web service that someone wrote that copied single row "files" from one machine to another. Due to a flaw in the logic, it would lose it's mind every once in a while and copy the same file quite literally a million times in a 24 hour period.

I do like Johan's extended event to find the source of this issue, whether internally or externally triggered. If, for some reason, that doesn't help then, as a last resort, you could also do the BSOFH thing to the problem. Turn off the mail and see which processes fail so that you know where they're all at.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)