sysmail_delete_log_sp (Transact-SQL)

Deletes events from the Database Mail log. Deletes all events in the log or those events meeting a date or type criteria.

Topic link iconTransact-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, and 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 does not 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

Only members of the sysadmin fixed server role can access this procedure.

Examples

A. Deleting all events

The following example deletes all events in the Database Mail log.

EXECUTE msdb.dbo.sysmail_delete_log_sp ;
GO

B. Deleting the oldest events

The following example deletes events in the Database Mail log that are older than October 9, 2005.

EXECUTE msdb.dbo.sysmail_delete_log_sp
    @logged_before = 'October 9, 2005' ;
GO

C. Deleting all events of a certain type

The following example deletes success messages in the Database Mail log.

EXECUTE msdb.dbo.sysmail_delete_log_sp
    @event_type = 'success' ;
GO

See Also

Reference

sysmail_event_log (Transact-SQL)
sysmail_delete_mailitems_sp (Transact-SQL)

Other Resources

Troubleshooting Database Mail
How to: Create a SQL Server Agent Job to Archive Database Mail Messages and Event Logs

Help and Information

Getting SQL Server 2005 Assistance