Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

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
add comment

1 Answer

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
add comment

Your Answer

 
discard

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.