Remove unsent database email from SQL Server

http://www.dotnet-tricks.com/Tutorial/sqlserver/4761260812-Remove-unsent-database-email-from-SQL-Server.html

uppose, 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.