Edit

sysmail_delete_log_sp (Transact-SQL)

  • Article

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.

Transact-SQL syntax conventions

Syntax

sysmail_delete_log_sp [ [ @logged_before = ] 'logged_before' ]
    [ , [ @event_type = ] 'event_type' ]
[ ; ]

Arguments

[ @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:

  • success
  • warning
  • error
  • informational

NULL indicates all event types.

Return code values

0 (success) or 1 (failure).

Remarks

Use the 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.

Permissions

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.

Examples

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

Related content