SQL Agent Job History Retention

Question

Post reply

SQL Agent Job History Retention

anzz

SSCertifiable

Points: 5503
More actions
October 21, 2010 at 11:42 am

#147386

On Sql server 2005 & 2008, we are getting only like 4-5 days worth for Job History Retention.
But we need like 4 weeks of SQL Agent Job History Retention.
I am doing like going SSMS --> SQL Server Agent Properties --> history--> enabling automatically remove agent history older then 4 weeks-> click OK.
again if I open the properties. I don't see the enabled automatically remove agent history older then 4 weeks.
what was the issue? what should I must do set my history for 4 weeks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

If you only have 4-5 days of history, then you need to increase the # of rows to retain on that same screen.

anzz SSCertifiable Points: 5503 More actions · Answer 2

Some job have like 4-5 history and some jobs don't have any history. So I want to set up at 4 weeks.

Adiga One Orange Chip Points: 27284 More actions · Answer 3

@ravz.sqldba, "Automatically remove agent history" option not remaining selected is a BUG which even I can reproduce. However when you select it and click ok, all data older than the selected duration (in your case 4 weeks) is deleted for one time.

Setting up a job which executed SP_PURGE_JOBHISTORY with the relevant parameters is a workaround.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

anzz SSCertifiable Points: 5503 More actions · Answer 4

@ Pradeep: I am not aware of SP_PURGE_JOBHISTORY.

what it will do. Is there any alternate solution of fixing the bug.

Adiga One Orange Chip Points: 27284 More actions · Answer 5

This article has the syntax for sp_purge_jobhistory. You may create a job which executes a query similar to the one below. This query deletes records older than 10 days.

DECLARE @oldestdate DATETIME

SET @oldest = GETDATE() - 10

EXEC SP_PURGE_JOBHISTORY @oldest_date = @oldestdate

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

maryanne_e_2000 SSC Veteran Points: 252 More actions · Answer 6

Does this same stored procedure (msdb.dbo.sp_purge_jobhistory procedure) also clean up SQL Server Reporting Services subscriptions managed by the SQL Server Agent Jobs?

Thank you.

Best Regards,

Mary Anne

forsqlserver SSCoach Points: 18902 More actions · Answer 7

forsqlserver

SSCoach

Points: 18902

June 28, 2016 at 12:35 pm

#1887410

Thanks Pradeep you are genious.

Thanks

Eric M Russell SSC Guru Points: 125560 More actions · Answer 8

Check your maintenance plan. Often times it will contain truncate job and backup history.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho