What are the sp_send_dbmail return values?

Error code 101 is because your query result attachment is larger than the "MaxFileSize" Database Mail configuration option.

Investigation

My first attempt at figuring this out was to run sp_helpttext on the sp_send_dbmail procedure to look at the implementation:

EXEC sp_helptext 'sp_send_dbmail';

The main body of that procedure returns a bunch of different status codes, from 0 up to 21. It doesn't mention 101. However, it calls into a few other stored procedures, like sysmail_verify_profile_sp, so I glanced in that one:

EXEC sp_helptext 'sysmail_verify_profile_sp';

But that only returns codes from 0 to 4.

Later the proc calls into sysmail_verify_addressparams_sp, let's check it out:

EXEC sp_helptext 'sysmail_verify_addressparams_sp';

Shucks, that only returns 0 or 1.

Next up is sp_RunMailQuery:

EXEC sp_helptext 'sp_RunMailQuery';

It calls sysmail_help_configure_value_sp and sp_isprohibited, both of which only return 0 or 1.

Black Box

At the end of sp_RunMailQuery, a system extended stored procedure is called: xp_sysmail_format_query.

We can't grab the source code for this, but I had a hunch from earlier in the proc:

--Get the maximum file size allowed for attachments from sysmailconfig.  
EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'MaxFileSize',   
                                            @parameter_value = @fileSizeStr OUTPUT

I noticed that you are using the attachment parameter, and the query results + max file size both get passed into the extended stored procedure as well.

Repro City

I set my Database Mail maximum file size to 10 bytes, and then ran:

DECLARE @result AS INT;

exec @result = msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMail',  
    @recipients = '[email protected]',  
    @subject = 'This is the subject',
    @body = 'This is the body',
    @body_format='html',
    @query = 'SELECT * FROM sys.messages;',
    @exclude_query_output = 1,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'big-file.csv',
    @query_result_separator = '===',
    @query_result_header = 1;

SELECT @result;

Note: the "sys.messages" table has a bunch of string data that I knew would be more than 10 bytes.

And sure enough, I got 101 as the return code:

Bugz

As CR241 helpfully pointed out, the documentation says that @@ERROR should contain the error id number that corresponds to the appropriate error message in the sys.messages table:

The error code for the statement that failed is stored in the @@ERROR variable. - sp_send_dbmail - Return Code Values

Instead it's 0 in my testing (which you noted in your original question). This seems like a bug to me.