Solved

How to clear filed or error Mail entries?

Posted on 2012-04-03
1
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 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