sql schedule jobs never finish (some times)

[tdrclan]

we have three sql servers.
one running win 2000 and sql 2000 (box P)
one running win 2003 and sql 2000 (box T)
the other win 2003 and sql 2008 (box O)


all servers talk(ed) to the database on an AIX box.

(box P) (jobs scheduled after 1 am)
running for 2 yrs no problem talking to database on AIX box (ever)

(box T) (jobs no longer scheduled)
running for 1 yrs installed sql 2005, ran for 3 months then started having regular issues talking to database on AIX box
removed sql 2005 installed sql 2000, no problem talking to database on AIX box. (stopped using DB that talk to AIX box on this machine)

(box O) (jobs scheduled after 5 pm)
running for 6 month installed sql 2008, run for 4 months then started having regular issues talking to database on AIX box.

the jobs are/were the same one all three boxes. ( running parallel in order to upgrade, if we can fix this problem. Running on only two boxes now)

problem:
for a while all jobs run without issues.
now the jobs ( 10+ ) just never finish.

normal run time:
some jobs only take a minute, others take 2 to 3 hours.
when problem occurs:
there are no jobs blocking.
the jobs just are sitting in a run state.
there are no error in the event log or default trace log.

the only thing I can see is that the jobs just don't finish.

what can I look at when this happens.
I don't see anything in sp_who2 or the windows events logs that point to any problems.

to kill the jobs, I sometimes need to reboot then everything run ok for a few days.

TIA

Tim

 

[DrSql]

After reading your case I think its more a source issue so I would review the following info.

Are there any concurrent limitations at the source?
Are there any other jobs running on the source or destination during that window?
Verify if there are any locks or blocks at the source
Verify the storage, CPU, Memory IO.
Also make sure that any local system level process are running, such as Anti-Virus update, SQL and VERITAS backups

Good Luck


Dr.Sql
Good Luck.

 

[tdrclan]

It could be a source issue, but the fact the sql 2000 server has never had this problem, leads me to believe that the issue might be in the 2005 and 2008 sql engines.


I will look into the source as suggested.

Thanks