Post reply

Clean up history

  • June 3, 2008 at 7:19 am

    #122517

    In SQL Server Agent - Jobs view history, SQL Agent, Job History and DatabaseMail

    I have a nightly job to delete the Database Mail but this seems not to be working as i have still all mail history since server started.

    DECLARE @delete_date datetime

    SET @delete_date = dateadd(dd,-1,getdate())

    EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before=@delete_date

    For Job History - i use the maintenance plan history clean up to do sql server agent job history, maintenance plan history and backup and restore history.

    How do you clean up sql server agent history log.---if i click on properties and set hisory automatically remove history 3 days when does this fire off. Hmm everytime i go in here and set flag and get out it is unchecked ? not retaining the flag

  • June 3, 2008 at 8:51 am

    #823669

    This made me curious, so I checked it out. I have a feeling this feature might be bugged.

    - I checked the flag, hit ok, and closed. Checkbox was not retained upon reopening.

    - I checked the flag, hit ok, closed, and then restarted the agent service. Same thing, no checkbox.

    - I checked the flag, hit ok, closed, and then restarted sql server svc (along with agent). Same thing still, no checkbox.

    Then, when scripting to a new query window, I scripted the change of retain one week. Date was 5/27/08 - correct.

    However, when scripting the change of 1 day, it went back 1 month, and vice versa - scripting change of 1 month actually scripted to retain only one day.

    Anyone know of this bug?

    "Got no time for the jibba jabba!"
    -B.A. Baracus

  • June 3, 2008 at 11:16 am

    #823766

    -- delete the emails

    DECLARE @delete_date datetime

    SET @delete_date = dateadd(dd,-1,getdate())

    print @delete_date

    EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before='Jun 2 2008 12:11PM'

    -- this deletes the logs

    EXECUTE msdb.dbo.sysmail_delete_log_sp

    GO

  • June 3, 2008 at 2:04 pm

    #823858

    Thanks for checking.

    -- delete the emails

    DECLARE @delete_date datetime

    SET @delete_date = dateadd(dd,-1,getdate())

    print @delete_date

    EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before='Jun 2 2008 12:11PM'

    -- this deletes the logs

    EXECUTE msdb.dbo.sysmail_delete_log_sp

    Which one will delete the SQL Agent history one.

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

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