SQL Server Database Mail 삭제 프로시저

There are two system stored procedures you can take advantage of in order to clean up Database Mail messages, ttachments and log entries stored in the msdb database. 

These two system stored procedures are sysmail_delete_mailitems_sp and sysmail_delete_log_sp.

You can execute the sysmail_delete_mailitems_sp stored procedure located in the msdb database to:

• delete all mail messages
• delete messages older than a given date
• delete messages with a given status or
• delete messages older than a given date with a certain status.

The complete syntax is shown below.

If you execute the sysmail_delete_mailitems_sp stored procedure without any parameters,

all mail messages will be deleted.

sysmail_delete_mailitems_sp
   [ [ @sent_before = ] 'sent_before' ] -- '1/1/2009'
   [ , [ @sent_status = ] 'sent_status' ] 
   -- sent, unsent, retrying, failed

You can execute the sysmail_delete_log_sp stored procedure located in the msdb database to:

• delete all log entries
• delete all log entries prior to a given date
• delete log entries for a certain event type or
• delete delete log entries prior to a given date for a certain event type.

The complete syntax is shown below. If you execute the sysmail_delete_log_sp

stored procedure without any parameters, all log entries will be deleted.

sysmail_delete_log_sp
   [ [ @logged_before = ] 'logged_before' ] --'1/1/2009'
   [, [ @event_type = ] 'event_type' ]
   --success, warning, error, informational

You should come up with a retention policy and schedule a job to run periodically to clean up the Database Mail history.

For example, the following script will delete all mail entries older than thirty days.

DECLARE @DeleteBeforeDate DateTime 
= (Select DATEADD(d,-30, GETDATE()))

EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate
EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate

 

Note: the syntax above in the first line is new for SQL 2008 where you can declare and set a value at the same time.

For SQL 2005 you would need to do this with two lines, first the DECLARE and then setting the value as follows:

DECLARE @DeleteBeforeDate DateTime 
SELECT @DeleteBeforeDate = DATEADD(d,-30, GETDATE())
EXEC sysmail_delete_mailitems_sp @sent_before = @DeleteBeforeDate
EXEC sysmail_delete_log_sp @logged_before = @DeleteBeforeDate

http://www.mssqltips.com/sqlservertip/1732/sql-server-database-mail-cleanup-procedures/

SQL Server Database Mail Cleanup Procedures