Thursday, May 15, 2014

Remove unsent database email from SQL Server

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.
  1. SELECT * FROM msdb.dbo.sysmail_event_log;
  2. -- To get number of unsent emails
  3. select count(*) from msdb.dbo.sysmail_unsentitems;
  4. -- remove all the unsent emails
  5. delete from msdb.dbo.sysmail_unsentitems;
Now all the unexpected email hav been removed from SQL Server database mail queue.

No comments:

Post a Comment