1

I'm using Win 2K3 R2 + SP2 with SQL 2K8 R2 installed.

I'm trying to set SQL Agent History to keep 1 week of logs. So here's what I do: After I connect, I right click SQL Server Agent and select Properties. After that I select History. Then I observe that no checkboxes are selected. I Then select the second checkbox (Remove Agent history:). And change the value of weeks to 1. And click OK.

After that I reopen to see that nothing was altered.

If that's not enough sometimes when I try to see the history of some particular jobs it timeouts.

I appreciate any help.

Thanks @Pradeep I've added two steps to my daily maintenance job here's the script for the tasks.

For purging the Backup History (optimizes backup speed)

declare @lastweek date;
select @lastweek = CONVERT(DATE,DATEADD(WEEK,-1,GETDATE()));
EXEC MSDB.DBO.SP_DELETE_BACKUPHISTORY @oldest_date = @lastweek;

For purging Job history I had to use a cursor.

declare @lastweek date
select @lastweek = CONVERT(DATE,DATEADD(WEEK,-1,GETDATE()));

DECLARE jobs_cursor CURSOR
FAST_FORWARD
FOR select job_id from msdb.dbo.sysjobs
DECLARE @job_id UNIQUEIDENTIFIER
OPEN jobs_cursor

FETCH NEXT FROM jobs_cursor INTO @job_id
WHILE (@@fetch_status <> -1)
BEGIN
    IF  (@@fetch_status <> -2)
    BEGIN
        EXEC msdb.dbo.sp_purge_jobhistory @job_id = @job_id, @oldest_date = @lastweek;
    END
    FETCH NEXT FROM jobs_cursor INTO @job_id
END

CLOSE jobs_cursor
DEALLOCATE jobs_cursor
GO

Hope this helps if anyone also needs this.

Gabriel Guimarães
  • 405
  • 5
  • 18

1 Answers1

2

This is a BUG as per this on MS Connect. Even I can reproduce this. When the check box is selected it purges the data for the date speicified but that option does not remain selected. So it purges the data only once when you select it and click on Ok. You can schedule a job which will execute sp_delete_backuphistory and sp_purge_jobhistory as a workaround.

Nomad
  • 471
  • 2
  • 3