Suppose, you are sending mail to different-different users by using while loop and you forgot to insert while loop update statement. In this case SQL Server will generate thousands or millions of mail against a specific email address with in a min.
To stop SQL Server for sending unwanted mails we required to clean the unsent mail from database mail queue. We can do this by running below queries.
SELECT * FROM msdb.dbo.sysmail_event_log; -- To get number of unsent emails select count(*) from msdb.dbo.sysmail_unsentitems; -- remove all the unsent emails delete from msdb.dbo.sysmail_unsentitems;
Now all the unexpected email hav been removed from SQL Server database mail queue.
In this article I try to explain how can you remove unsent email from database mail queue. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.