Post reply

SQL Database Mail Log is Large

  • February 17, 2009 at 4:59 pm

    #206622

    The Database Mail Log is showing everything since the day SQL was installed with more than 20,000 records. How do I maintaince the log? Thanks.

  • February 17, 2009 at 8:06 pm

    #945622

    see http://www.sqlservercentral.com/Forums/Topic527870-146-1.aspx, specifically Todd Engen's note.

    -- to remove mail more than a week old, this will do:

    DECLARE @SentBefore datetime

    SET @SentBefore = DateAdd(day, -7, DateAdd(day, DateDiff(day, 0, getdate()), 0))

    EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @SentBefore;

  • February 18, 2009 at 11:27 am

    #946162

    Thanks.

    I tried to delete all mail items before I posted the question but almost 24 hours later sp_who still shows a DELETE is in suspended status. When I query sysmail_alltimes I got only few rows but when I view Database Mail log it still shows 20K plus rows. I did backup msdb and truncate the log but it won't go any further. Should I restart SQL service to commit the change?

  • February 19, 2009 at 2:57 pm

    #947178

    the log is empty after I restarted SQL service. Thx.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply