Removing History of Database Mail Log

You might know that it is possible to remove history of an Agent Job history.

 

 

If you right click the Agent properties and select History. There you have a configuration to limit you job history.

If you go to the Database mail history, you will see that above setting does not delete the database mail log.

So question is how we can delete the database mail log so that it can be maintained.

well, there is no user interface for that.   However, in SQL Server there is a in built procedure in MSDB database called sysmail_delete_log_sp

This proc has two default parameters, @logged_before (datetime)  and @event_type (varchar(15)) and both has default values of null.

so, if you simply execute sysmail_delete_log_sp it will simply delete entire log.

if you pass the date for the @logged_before  , your database mail log data before that will be deleted.

sysmail_delete_log_sp @logged_before = '2010-12-31' 

Above will delete all the log data before 2010-12-31.  

If you want to delete data until up to last seven days, you can execute following.

DECLARE @dt DATE =  DATEADD(d,-7,GETDATE())

EXEC sysmail_delete_log_sp  @logged_before = @dt
 

Also, you can delete database mail log based on the event type as well. You can pass Success, Information or Warning. 

if anything else other than NULL is passed it will be treated as Error.