Remove unsent database email from SQL Server

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

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.

Take our free skill tests to evaluate your skill!

In less than 5 minutes, with our skill test, you can identify your knowledge gaps and strengths.

Learn to Crack Your Technical Interview

+
+
Accept cookies and close this message