SQL Agent job hanging, please help

[peac3]

Hi All,

I have 2 SQL Agent jobs and both jobs generate chunk jobs:
The first one has 7 chunks and the second one has 2 chunks

After the first Agent job completed (including the chunks) and I ran the second Agent job after it generates the chunk it hangs without any error.
Then if I stopped it and rerun for the second time, it runs fine.

I put the trace on to track what would be the root cause, I haven't got any much information.
I was wondering what would be the difference between first run and second run. Does sql have some cache or memory that cause the hang? if it does, how do we clear it up?

Any input will be greatly appreciated...

Thanks

 

[SQLBill]

Sometimes there are errors that aren't captured by the job error message. Go to each of the steps, click on Advanced, then check the box 'Log to table'. Also select the 'Append output to existing entry in table'. Then when the job fails, go back to the Advanced window and click on View. That might give you more information about the error. One thing to remember...if a job has hung...there's no error, it's still doing something. It could be waiting for something to finish. Just because a job finishes, doesn't mean the process/code it is running has finished.

Let's say your first job starts a stored procedure....the job may start it running and once it is running, the job can complete as successful, but the stored proc may still be running.

Some things to try, in the first job, create a final step that starts the second job. Or put a WAITFOR delay step in the second job.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875