Hi Chandana,

Can you try to create a simple package with just a Execute SQL task and run it from a job and see if this gives an issue.

Can you schedule a profiler to run the same time as this job and check the results on the day it hangs. This might give you some insight on it.

You can also check the dynamic management views to see for information about blocking and locking that is happening and more information from them.

Please let me know if you need more information.

Thanks,

Lakshmi