Solved

How to clear filed or error Mail entries?

Posted on 2012-04-03
1
237 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

17 Experts available now in Live!

Get 1:1 Help Now