June 24, 2010 at 2:13 am
There is a SQL job which calls SSIS package. The job hangs indefinitely randomly on somedays. The SSIS is stuck at SQL execute task which calls a stored proc. The stored proc is a simple procedure to insert and update a table. There is no input parameters to it nor output values.. still it gets hung indefinitely...
Note: This also happens with few other job which are calling SSIS and it hangs in the SQL execute task only..
The workaoround is currently to stop the SQL job and execute the procedure manually from Query analyser and it finishes in seconds... and then recover the next steps in the SSIS manually 🙁
Has anyone faced such issue?
June 24, 2010 at 3:33 am
Most of the times when my packages end up hanging, it has something to do with locks.
Either a deadlock or a table lock that isn't released by another job...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 24, 2010 at 3:38 am
There is no blocking/locking in this case.. Even if this is the only statement running.. like insert or update in that stored proc.. it is simply hung...
June 28, 2010 at 3:55 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply