Microsoft SQL Server on Windows Azure Virtual Machines

The writers of this blog are a part of the Microsoft SQL Server team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Server components on Windows Azure Virtual machines

SQL Database Mail -- Cleanup of Logging Records

SQL Database Mail -- Cleanup of Logging Records

  • Comments 1

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>

Leave a Comment
  • Please add 7 and 5 and type the answer here:
  • Post
  • Thanks for posting this. Our log had gotten so large, our mail was periodically stopping! Clearing it allowed it to run again.

Page 1 of 1 (1 items)