Troubleshooting Missing Mail From Queue in SQL Server

When sending mail, SQL Server assigns a mail ID to the job e.g 1049:

EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'BackupEmail',
	@recipients = 'xxxxxxxxxxxxxx@gmail.com',
	@body = 'Test from TSQL Code',
	@subject = 'I am a test email';

However, when viewing the Database Mail Log, you may not find it:

In the image above, the last mail to be sent has the ID of 1039 which means there are 10 mails missing from the log. Use the following stored procedure to confirm that there are 10 mails in the queue:

EXEC msdb.dbo.sysmail_help_queue_sp;

To find the missing mail, use the query:

select * from msdb.dbo.sysmail_allitems order by mailitem_id desc;

To troubleshoot this, use the following query to find the profile it was sent from and its status:

select A.mailitem_id, P.name, A.sent_status 
from msdb.dbo.sysmail_allitems A
inner join msdb.dbo.sysmail_profile P
on  A.profile_id = P.profile_id
order by mailitem_id desc;

If it is only one particular profile that refuses to send the mail, go through the email setup checklist to ensure that the profile has the correct settings. However, if you have setup the profile correctly, you may have to restart the mail service:

EXEC msdb.dbo.sysmail_stop_sp;
EXEC msdb.dbo.sysmail_start_sp;

This may not work or can take a very long time to execute so the next best thing may be to restart the SQL Server Agent service.
Now if I view the Database Mail Log again, I will see the mails that were in the queue (you may have to wait about 20 minutes after the restart for this to happen):

The jobs show that they failed but that is a different problem.