Missing History for Jobs (msdb.dbo.sysjobhistory)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

thejamie - 26 Nov 2007 15:49 GMT

New server running our SQL... upgrade from Advanced 2000 to Enterprise
2003... our SQL Instance normally records all history (View History from Jobs
in SQL Agent). For some reason, currently unknown, history disappears every
so often. In this case, all history from 11/24 going backward is gone. All
history from 11/25 through today is here. This happened previously. Is
there any logical explanation for this? I'd settle for a server reboot, but
to my knowledge, this did not happen either.

Also there is a disk space problem on one of the drives. The msdb does not
touch that drive so, in my way of thinking the drive space issue cannot be
connected to the missing history (sysjobhistory). Is this logical to assume?

An additional thought comes to mind. The jobs were imported from one server
to another. The owner was either the service account (with plenty of
permission) or the sa account, but the jobs were recreated from script using
the sa login. Could this have an effect on the job history?

Signature

Regards,
Jamie

Reply to this Message

thejamie - 26 Nov 2007 15:55 GMT

Also I believe this is happenning each week. Saturday night or Sunday
morning to be sure.

Signature

Regards,
Jamie

> New server running our SQL... upgrade from Advanced 2000 to Enterprise
> 2003... our SQL Instance normally records all history (View History from Jobs
[quoted text clipped - 12 lines]
> permission) or the sa account, but the jobs were recreated from script using
> the sa login. Could this have an effect on the job history?

Reply to this Message

Russell Fields - 26 Nov 2007 16:32 GMT

thejamie,

Recreating the jobs would cause them to have a new job_id, so any history
records that might still exist in sysjobhistory will no longer join to
existing jobs. (If you still have your previous server and can figure out
the old job_id to new job_id mapping, you could update rowsn in your
sysjobhistory and all would be well.)

Also, make sure that your SQL Agent Properties - History settings are the
same on you new server as your old, or you will find SQL Agent (by default)
deleting history fairly frequently.

RLF

> New server running our SQL... upgrade from Advanced 2000 to Enterprise
> 2003... our SQL Instance normally records all history (View History from
[quoted text clipped - 20 lines]
> using
> the sa login. Could this have an effect on the job history?

Reply to this Message

thejamie - 26 Nov 2007 17:57 GMT

Thanks for the reply Russell. Actually, I moved the data over on 11/4. I
didn't expect any history to go back before the move. What bothers me is
that on Saturday I had history going back to 11/4. On Sunday, it extended
only from 11/25. Everything prior to 11/24 is wiped. It still exists in
sysjobhistory. I am not the sole DBA so it is possible that someone other
than myself wiped it, but I have to presume that is not the case. It isn't
vital. I can filter the log for a given job to get the history. The history
was set to 1000 - I have lots of disk space at the moment so I moved it to
10000 and from 100 to 1000 on the rows. Still a mystery. I suspect that
the disk space on the one drive being low may have something to do with the
history disappearing.

Signature

Regards,
Jamie

> thejamie,
>
[quoted text clipped - 34 lines]
> > using
> > the sa login. Could this have an effect on the job history?

Reply to this Message

Russell Fields - 26 Nov 2007 18:16 GMT

Jamie,

If the rows are still in sysjobhistory then they should connect to the jobs.
If the jobs will not show history further back than 11/24, then did someone
redeploy the jobs again, such that they got new job_ids?

RLF

> Thanks for the reply Russell. Actually, I moved the data over on 11/4. I
> didn't expect any history to go back before the move. What bothers me is
[quoted text clipped - 55 lines]
>> > using
>> > the sa login. Could this have an effect on the job history?

Reply to this Message

thejamie - 26 Nov 2007 18:56 GMT

Sounds very possible. Thanks.

Signature

Regards,
Jamie

> Jamie,
>
[quoted text clipped - 63 lines]
> >> > using
> >> > the sa login. Could this have an effect on the job history?

Reply to this Message