Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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()));

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
FOR select job_id from msdb.dbo.sysjobs
OPEN jobs_cursor

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

CLOSE jobs_cursor
DEALLOCATE jobs_cursor

Hope this helps if anyone also needs this.

share|improve this question
Your WHILE loop is missing a fetch =) – anon May 14 '11 at 18:00
Thanks @anon, how did that happen, lol. – Gabriel Guimarães May 16 '11 at 13:25
up vote 2 down vote accepted

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.