Remove Unsent Database Email from SQL Server

Remove Unsent Database Email from SQL Server

19 Mar 2024
Intermediate
159K Views
4 min read

How to Remove Unsent Database Email from SQL Server?

Suppose, you are sending mail to different users by using a while loop and you forgot to insert a while loop update statement. In this case, SQL Server will generate thousands or millions of emails against a specific email address within a minute.

Read More: SQL Server Interview Questions and Answers

To stop the SQL Server from sending unwanted mail we are required to clean the unsent mail from the database mail queue. We can do this by running the 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; 

Read More: Basics of SQL Commands

Explanation of the Code

  1. Retrieving Unsents Email Messages:
    
    SELECT * FROM msdb.dbo.sysmail_event_log;
    

    This query retrieves information about email events from the sysmail_event_log table. It can be useful for monitoring the status of email operations.

    
    select count(*) from msdb.dbo.sysmail_unsentitems;
    

    This query calculates the number of unsent email messages in the sysmail_unsentitems table.

  2. Removing Unsents Email Messages:
    
    delete from msdb.dbo.sysmail_unsentitems;
    

    This statement deletes all unsent email messages from the sysmail_unsentitems table, effectively clearing the email queue.

    All the unexpected emails have been removed from the SQL Server database mail queue.

Summary

In this article I tried to explain how you can remove unsent emails from the database mail queue. If you want to gain a practical understanding, you can enroll in our SQL Server Course.

Do you Know?

.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.

Therefore, if you want to upskill yourselves and stand out from others consider our following training programs on .NET.

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE

Share Article
Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Self-paced Membership
  • 22+ Courses
  • 750+ Hands-On Labs
  • 300+ Quick Notes
  • 55+ Skill Tests
  • 45+ Interview Q&A
  • 10+ Real-world Projects
  • Career Coaching
  • Email Support
Upto 66% OFF
KNOW MORE..

To get full access to all courses

Accept cookies & close this