New server running our SQL... upgrade from Advanced 2000 to Enterprise
2003... our SQL Instance normally records all history (View History from Job
s
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?
--
Regards,
JamieAlso I believe this is happenning each week. Saturday night or Sunday
morning to be sure.
--
Regards,
Jamie
"thejamie" wrote:
> New server running our SQL... upgrade from Advanced 2000 to Enterprise
> 2003... our SQL Instance normally records all history (View History from J
obs
> in SQL Agent). For some reason, currently unknown, history disappears eve
ry
> so often. In this case, all history from 11/24 going backward is gone. A
ll
> 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, b
ut
> to my knowledge, this did not happen either.
> Also there is a disk space problem on one of the drives. The msdb does no
t
> 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 assu
me?
> An additional thought comes to mind. The jobs were imported from one serv
er
> to another. The owner was either the service account (with plenty of
> permission) or the sa account, but the jobs were recreated from script usi
ng
> the sa login. Could this have an effect on the job history?
> --
> Regards,
> Jamie|||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
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:C078BDDA-ED20-4F42-AD0E-2FF1AED4EE7F@.microsoft.com...
> 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?
> --
> Regards,
> Jamie|||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 histor
y
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.
--
Regards,
Jamie
"Russell Fields" wrote:
> 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
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:C078BDDA-ED20-4F42-AD0E-2FF1AED4EE7F@.microsoft.com...
>
>|||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
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A8C507C7-D1B6-46FF-AD2F-9E9EBCF829F5@.microsoft.com...[vbcol=seagreen]
> 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.
> --
> Regards,
> Jamie
>
> "Russell Fields" wrote:
>|||Sounds very possible. Thanks.
--
Regards,
Jamie
"Russell Fields" wrote:
> Jamie,
> If the rows are still in sysjobhistory then they should connect to the job
s.
> If the jobs will not show history further back than 11/24, then did someon
e
> redeploy the jobs again, such that they got new job_ids?
> RLF
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A8C507C7-D1B6-46FF-AD2F-9E9EBCF829F5@.microsoft.com...
>
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment