Hi, I have a SQL Agent Job with 5 steps running several SSIS Packages on MSDB. Today the 3rd of 5 Steps did not finish and the Job was hanging in this state. I checked the sysssislog where I log the onPostExecute events and saw that the PackageEnd Event was logged as always. So it seems like the Job Agent did not notice the end of the step and does wait forever. After I stopped the Job and restarted it beginning with step 4 it went through like it should. I noticed this behaviour in some rare cases some time ago, but it never had a bigger impact. But now I want to identify and solve this problem. Does anyone have an idea what else I could check or ist there anyone that had the same problem?

Hi, did you check about the execution of the job, may it waiting for resources that used by another job. check the lock using DMVs and ensure it does not wait for resources. I hope this is helpful. Elmozamil Elamir MyBlogPlease Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem Elmozamil Elamir Hamid http://elmozamil.blogspot.com

Thank you Elmozamil I did check the activity monitor while the job was still in running state (uses same sources like DMVs ?!) and there where no active processes on my database. What else can I check? I found the same behaviour on the net by a R2 User: http://www.networksteve.com/enterprise/topic.php/SSIS_Package_/_SQL_Agent_Job_never_ends_on_SQL_2008_R2/?TopicId=40067&Posts=4

Jay, I would like to ask you to list more details re what 3rd step's SSIS package encompasses. In all cases my approach is to add more logging, and in most cases I would find that it is a Script Task trying to raise a graphical error message that naturally no one can dismiss when raised from within the Agent. So two thing: Package design, and After you added more logging post whatever is relevant under the circumstances when the package stalled. Arthur My Blog

The 3rd step runs a ssis package, which is just running 5 other ssis packages. Those packages are aggregating data in different tables and moving data between sql machines. One of those packages includes processing ob a ssas cube. No script task was used in the packages. So most of it are simple data flows and sql tasks. I will add more logging in the ssis package, so i will get also informational log details