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

  • 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.

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

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • No, I did not.

  • 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.

  • 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

  • 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.

  • 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

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

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

  • 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.

  • 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.

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

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