Remove unsent database email from SQL Server

Shailendra Chauhan  Print   1 min read  
26 Aug 2012
25 Sep 2012
Intermediate
139K

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.

Learn to Crack Your Technical Interview

 
Learn to Code

Start your journey today to learn coding!


Because learning to code is the first step and foreward to advance your career.


 GET STARTED NOW 

+
+