Solved

How to clear filed or error Mail entries?

Posted on 2012-04-03
1
234 Views
Last Modified: 2012-06-21
Hi,
The query below generates tonnes of entries for me to look thru. I can add filter there but I don't need to keep failed entries after I resolved the issue.

How can I get rid of error mail entries?

SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO
0
Comment
Question by:crazywolf2010
1 Comment
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 500 total points
ID: 37800724
You can delete entries from sysmail_mailitems table using "sysmail_delete_mailitems_sp" stored proc:

exec sysmail_delete_mailitems_sp @sent_before, @sent_status
For example, exec sysmail_delete_mailitems_sp @sent_status = 'failed' will delete all failed mail entries. Allowed values for status are unsent, sent, failed, retrying.

Similarly, you can use "sysmail_delete_log_sp" sp for deleting entries from log:
exec sysmail_delete_log_sp @logged_before, @event_type

Allowed values for event type are success, warning, error, information.

PS: There are many usefule sp's in msdb which will help you to deal with db mails.
select * from sysobjects where name like '%mail%' and type = 'P'

Thanks,
Harish
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now