:::: MENU ::::

dbmail error File attachment or query results size exceeds allowable value of 1000000 bytes

  • Jan 31 / 2014
  • 0
Database Mail, dbDigger, SQL Server Agent scheduled Jobs, SQL Server Error messages

dbmail error File attachment or query results size exceeds allowable value of 1000000 bytes

Last day i was working on a task which was meant to generate some data and send it as an email attachment to a specific email address. This process was going to be configured as a scheduled job in SQL Server agent. I completed the first part of task and data was ready as a CSV file to be send through email.

Problem Phase

In the second phase while trying to send the file as an attachment i got following error message.

dbmail error File attachment or query results size exceeds allowable value of 1000000 bytes

Amount of bytes shows that about 1 MB file size cap was there on attachment file that should be send through DBMail. In my case we were expecting the attachment file of size up to 25 MB.
So the questions are

  • Can we change the default limit on attachment size in database mail .i.e. 1000000 bytes?\
  • If yes then where can we change this parameter?

Solution

Yes we can change the default limit on attachment size in database mail. And related to the procedure of changing this parameter following are the steps

  • Open SSMS and go to Management folder
  • Right click on Database Mail and select Configure Database Mail
  • Click Next and select view or change system parameters radio button
  • Click next and you will be presented the parameters screen where you can view and modify the parameters as well
  • Here you can change the value against the Maximum File Size (Bytes) parameter
  • Specify the required size in bytes and click Next to save the configurations

Now you can send attachment up to mentioned max size here without any error.

What is the limit of Max value?

It is a valid question here at this point that what is the limit of value that we can enter as maximum file size in bytes? The answer is 2147483646.
Yes this parameter is of type int and can accept value under 2^31-1 (2,147,483,647) bytes. This mean that roughly you can get about 2GB size against this parameter. If you enter exceeded value for size then following error message will be generated

Account Retry Attempts, Account Retry Delay, Maximum File Size, Database Mail Executable Minimum Lifetime, and Maximum Number of Mails must have valid values to continue.

Value was either too large or too small for an Int32. (mscorlib)

Consult us to explore the Databases. Contact us