Remove unsent database email from SQL Server

 Print 
  Author : Shailendra Chauhan
Posted On : 26 Aug 2012
Total Views : 132,649   
Updated On : 25 Sep 2012
 

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.

Summary

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.



Free Interview Books
 
COMMENTS
Rick Willemain Reply 378 days 6 hours 24 mins ago

Thank you for the article. In a case like this, would it be a good first step to stop the mail processing before the clean-up and code fix are completed (( assuming no other job/process will get negatively affected by the stopped db mail )) ? Any simple cmd to disable / enable ?

SUBSCRIBE TO OUR YOUTUBE CHANNEL
 
 
 
LIKE US ON FACEBOOK
 
+