I had an issue since last week wherein the last received e-mail was last Dec 29.

The job had no problem since it was created (months ago).

The problem is we haven't receiving the e-mail even when the status of the job had no problem.

Below is the script:

USE msdb
EXEC sp_send_dbmail 
@subject='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx Message',
@body='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx Message',

When I'm doing a trial and error in my local database, it really was being run without error but still not receiving the e-mail. But when I tried to change the body/subject (I tried to shortened it), I am now receiving the e-mail.

When I checked though the documentation of MS, limit of body is NVARCHAR(MAX) yet my original body subject is far to reach the limit.

What seems to be the problem?

Take note of the following:

  1. No problem in mail server since I can send with shortened body / subject.
  2. Attachment is present in the shared folder.
  3. Shared folder is accessible.
  4. No problems with database mail profile and account.

I've already checked dbo.sysmail_allitems and its status is 'SENT'

  • Can you run the following against the msdb database to have an idea of the status of the mail queue. sysmail_help_queue_sp @queue_type = 'Mail' ; Does Message Status shows as unsent? retrying? sent? failed? – Asteway Jan 4 '18 at 12:13
  • What is the lenght of the @subject variable? Are there limitations to the attachment regarding size? Did someone change a setting on the mailserver or firewall that prevents mails with attachment to be sent or processed. Maybe some anti-virus that is blocking your mail? – Reitse Eskens Jan 4 '18 at 12:48
  • length of subject and size of the attachment is not an issue since its been running for months since job was created. Im guessing its network related issue / firewall issue that was reconfigured recently. Will coordinate this with the network and system admins. – Edgar Allan Bayron Jan 5 '18 at 4:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.