Solved

How to clear filed or error Mail entries?

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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