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.
Now all the unexpected email hav been removed from SQL Server database mail queue.
- 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;