sp_send_dbmail: Failing When Adding Attachment

Question

I have a simple script that should send an attachment using send_dbmail. It works when I exclude the attachment or send a very small file, e.g. 5KB. The prod file I want to send is 280KB.

    Exec msdb.dbo.sp_send_dbmail
    @profile_name = 'Activeprofile',
    @recipients = 'Dave@Dave.com.au',
    @subject = 'EmailStuff',
    @file_attachments = 'D:\SSIS\SSISData.csv'

The Database mail configurations are default at 1MB file sizes and the only error I get in the database mail log is

Message The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2020-07-22T15:25:34). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )

This is SQL 2016 SP1 CU5 (Yes I want to patch but management are 'scared') The process fails running from SQL Agent Job or Query window.

The email targets an internal relay server. I am able to send the file using a test SQL server running SQL 2017, I was wondering if this was a bug with SQL 2016.

Answer 1

After fighting with this for half a day I took SQL out of the equation and tried to perform the same task using Power Shell. This also failed, my network guy ran a PCAP between SQL and relay server and we have identified the issue.

File was getting to the relay server but for whatever reason on that particular SQL server (AWS) by the time the file made it to relay the file was fragmented, wasn't sending the full file.

This SQL box was built before my time by someone who 'DID' SQL, it has several setup and config issues so I am pushing for a rebuild.