sysmail_delete_log_sp (Transact-SQL)

 

Updated: June 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

  
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 success, warning, error, and informational. NULL indicates all event types.

0 (success) or 1 (failure)

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.

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

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  

sysmail_event_log (Transact-SQL)
sysmail_delete_mailitems_sp (Transact-SQL)
Create a SQL Server Agent Job to Archive Database Mail Messages and Event Logs

Community Additions

ADD
Show: