Job History disappearing with settings of 50000 total and 1000 per job

Question

Post reply

Job History disappearing with settings of 50000 total and 1000 per job

tim.steorts

Old Hand

Points: 365
More actions
January 3, 2011 at 8:22 am

#146740

Please help me figure out why there are only 652 rows in sysjobhistory, and none older that 12-29-2010, when the SQL Server Agent settings are 50000 for "Maximum job history log size (in rows)" and 1000 for "Maximum job history rows per job".
There are no cleanup jobs or cleanup maintenance plans that I can find.

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

Did you click on "Remove Agent History" and click Ok. If yes, this will clean up history.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

tim.steorts Old Hand Points: 365 More actions · Answer 2

tim.steorts

Old Hand

Points: 365

January 3, 2011 at 8:54 am

#1268507

No, I did not.

Steve Jones - SSC Editor SSC Guru Points: 734794 More actions · Answer 3

I'm assuming this isn't a growth issue in msdb and new history notifications are being logged.

My guess is someone cleaned it out. The default trace is probably too old for you, but what you could do is start service side traces for changes to msdb and see if there is some cleanup job that you are not aware of.

nekonecny Right there with Babe Points: 720 More actions · Answer 4

You need monitor executing sp_purge_jobhistory like this:

declare @dt datetime

select @dt = getdate()-30

EXEC msdb.dbo.sp_purge_jobhistory @oldest_date=@dt

tim.steorts Old Hand Points: 365 More actions · Answer 5

nekonecny,

I don't want to purge job history. I am trying to determine why the history is disappearing before coming anywhere near the limits I have set.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 6

Do you have any maintenance plans that have cleanup tasks?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

tim.steorts Old Hand Points: 365 More actions · Answer 7

No, there are no cleanup jobs or cleanup maintenance plans that I can find

brian.keyworth SSC Journeyman Points: 88 More actions · Answer 8

Did you find out what was causing this? I am having the same issue.

tim.steorts Old Hand Points: 365 More actions · Answer 9

The settings on the SQL Server Agent Properties, History, were the problem. Apparently someone (or something?) else had been changing them after I checked them. I don't have the problem any more.

brian.keyworth SSC Journeyman Points: 88 More actions · Answer 10

OK Thanks.

I am going crazy trying to find out the issue.

We have deployed 96 virtual Servers in 26 remote offices with identical SQL2008R2 scripted builds and everyone of them is deleting all SQL Agent history once per week at different times! I have checked maintenance plans for history cleanups, SQL agent properties, SQL jobs, etc, etc and still cant find what it is!

Oh well, back to the search.