SQL Database Mail — Cleanup of Logging Records


Similar to SQL Agent logging history, Database Mail has own logging history.  When the logging history is grown too big, you can run T-SQL sysmail_delete_log_sp to clean the logging records.  Several examples of using this stored procedure are provided here.

The article here is assumed that a SQL Database Mail profile “MailProfile1′ has been created.  The MailProfile1 tells Database Mail what account it should use to send emails.  For how to create a Database Mail profile, you can refer to the previous post SQL Database Mail – Send Emails from SQL Server.

To delete all records that are generated before 2:37pm, January 17, 2011, run sysmail_delete_log_sp as follows.

— Start T-SQL

    USE msdb
    EXEC sysmail_delete_log_sp @logged_before=’2011-01-17 14:37:00′ 

— End T-SQL —

Below is the comparison of log history show before and after deletion.

Before deleting:

After deleted:

 

Another example is to delete all informational log records.

— Start T-SQL

    USE msdb
    EXEC sysmail_delete_log_sp @event_type=’information’ 

— End T-SQL —

For more information about sending emails, see sysmail_delete_log_sp in MSDN documents.

<END OF POST>


Comments (1)

  1. Margaret says:

    Thanks for posting this. Our log had gotten so large, our mail was periodically stopping! Clearing it allowed it to run again.