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.
.gif "Topic link icon")
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, 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