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.