Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

How to clear filed or error Mail entries?

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
crazywolf2010
Asked:
crazywolf2010
1 Solution
 
Harish VargheseProject LeaderCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now