I have a DTS job that can run many times successfully, but every few days it
fails.

When it fails, the process is using all of one of the CPU's (I have a 4 CPU
machine), but is using very little memory and has zero I/O!

I have to stop and start the SQL Service before everything will return to
normal. Simply killing the job in question and resubmitting it does not work.

The DTS job in question does the following-

Drops the table
creates it again
uses a view to populate the table

When the job fails, it has dropped and recreated the table but has not
populate it with any data.

The SQL server in question is being used by our Great Plains system

Does anybody have any idea what might be wrong? It seems to me that the DTS
job in question is not the root cause of the problem, but that something else
puts the SQL server into a state whereby it cannot service the DTS job. I
think this because it requires a restart of the SQL service to start working
again.

Any pointers gratefully received

Cheers,

Bob

I should also say that we are running sql 2000 sp3a +hotfix

1. what error does it fail with?
2. if it just hangs, do you have a msgBox in your code somewhere (this
wont explain why the SQL Service would hang, but might explain the
DTS hanging)

-blair.

"Bob Findlay" <BobFindlay (AT) discussions (DOT) microsoft.com> wrote

4) while generating ZERO i/o and very little memory usage.