Hi,

I am running a dozen or so of DTS Scheduled jobs which call a DTS
package each. The packages basically are pooling data from views of the
Production server with SQL Server 2000 to another Reporting Server with
SQL Server 2000 again (Refer a representation below).


Production Server(SQL Sever 2000) View ------DATA-----> Tables of
Reporting Server(SQL Sever 2000)

The packages have logging enabled. The jobs are scheduled to run at 1
AM everyday. The problem which I am seeing is that the jobs seem to run
fine most of the times. But sometimes sporadically 1 or 2 jobs (without
much of a pattern of which job it is) HANG up. HANG in the sense; the
jobs are the Execution state for more than 10-15 hours, without ever
completing. I have error mailing enabled in the jobs, but since jobs
(which hang) are typically not failing, so I am not getting notified
that the jobs did not complete. If I just go ahead and stop the job,
restart it again it runs through successfully!

My questions are
1. Why do these jobs HANG up? Is there anything I can do to avoid this
from happening?
2. Can I can automate the process of checking that jobs did not
complete after a particular interval of time, then stop the job and
restart the job again.
3. How can I get notified by e-mail if these jobs remain in the HANG
state after a particular interval of time since the job started?

Thanks a lot for your thoughts!