SQL Job History ???

[ezecastle]

I have a question about the sql job history. I have a sql 2000 server that was recording all the job history on my sql jobs just fine until the other day. Today I checked the history and the jobs didnt show the past history anymore. Just today's history. So if the job didnt run today there is no history for that particular job. This isn't true, the job was run a few days ago. I know that no one went in and cleard the history on the jobs. What causes this issue, is there a way to not make this happen in the future? Is there a way I can recover the past history for my jobs.

Thanks for your time!! TIA

[skhanal]

Did anyone go to the sql agent properties and clear the log or changed the number of rows to 0 and change it back to default?

You can restore msdb database and see sysjobhistory table.

[ezecastle]

Thanks for the response, As far as I know no one on my DBA team changed anything in the agent properties. If i restore the msdb database i will lose today's history correct.

[rmiao]

Sql agent keeps 1000 most recent job history entries by default, you can increase it or set unlimit in sql agent properties -> job system tab.

[ezecastle]

If the job history has a limit of rows the default is 1000 rows. Once this limit is reached does the log get reset? This might explain it.

[ezecastle]

Wouldn't it make sense that the log wouldnt clear itself but just get rid of the oldest event and continue to write to the log fine?

[rmiao]

That's what sql agent does, delete old history entries from msde..sysjobhistory. Those deleted entries maybe for same job.

[ezecastle]

right, say you have 100 jobs; you look at the job history for all jobs and the past history was cleard for some reason. Now you only see the history for the jobs that ran today. why whould that happen if no one cleared the log?

[rmiao]

Since sql only holds 1000 most recent job history entries by default, it'll delete old entries automatically.

[ezecastle]

today i logged into the server to see if the 18th job history date was still in the jobs. Nope! just says the 19th and didnt keep the 18th. Any ideas? This one has me

[rmiao]

How many rows do you have in msdb..sysjobhistory?

[skhanal]

Check the count of rows in sysjobhistory. May be some jobs are running too frequently and taking up all available rows in sysjobhistory.