Applies to: SQL Server
Deletes events from the Database Mail log. Deletes all events in the log or those events meeting a date or type criteria.
sysmail_delete_log_sp [ [ @logged_before = ] 'logged_before' ] [ , [ @event_type = ] 'event_type' ] [ ; ]
[ @logged_before = ] 'logged_before'
Deletes entries up to the date and time specified by the @logged_before argument. @logged_before is datetime with NULL as default. NULL indicates all dates.
[ @event_type = ] 'event_type'
Deletes log entries of the type specified as the @event_type. @event_type is varchar(15) with no default. Valid entries are:
NULL indicates all event types.
Return code values
0 (success) or
sysmail_delete_log_sp stored procedure to permanently delete entries from the Database Mail log. An optional argument allows you to delete only the older records by providing a date and time. Events older than that argument will be deleted. An optional argument allows you to delete only events of a certain type, specified as the @event_type argument.
Deleting entries in the Database Mail log doesn't delete the e-mails entries from the Database Mail tables. Use sysmail_delete_mailitems_sp to delete e-mail from the Database Mail tables.
This stored procedure is owned by the db_owner role. You can grant EXECUTE permissions for any user, but these permissions may be overridden during a SQL Server upgrade.
A. Delete all events
The following example deletes all events in the Database Mail log.
EXEC msdb.dbo.sysmail_delete_log_sp; GO
B. Delete the oldest events
The following example deletes events in the Database Mail log that are older than October 9, 2022.
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = 'October 9, 2022'; GO
C. Delete all events of a certain type
The following example deletes success messages in the Database Mail log.
EXEC msdb.dbo.sysmail_delete_log_sp @event_type = 'success' ; GO