none
DTS Package hangs while running using SQL Server Agent Jobs

    질문

  • Hi everyone

    I have a DTS package which is scheduled to run every 15 minutes. Sometimes it hangs and we have to kill it manually. I have a Execute SQL Task which runs a batch file and Send Mail Task along with other tasks. What might be the possible casue for this? What are the parameters I have to look for?

    Is that because the target DB is down at the time of run. Please give your suggestions. Let me know if you want more details.

    Thanks in advance 


    Sabi

    2012년 4월 19일 목요일 오후 3:49

답변

모든 응답

  • You need to enable logging in your ssis package and then examine the logs to see where it is "hanging" .  For starters turn on all of the events.  Then when it hangs you can just open the log file while the process is hung and it'll show you where it is at.

    Chuck


    2012년 4월 19일 목요일 오후 3:55
  • Hi everyone

    I have a DTS package which is scheduled to run every 15 minutes. Sometimes it hangs and we have to kill it manually. I have a Execute SQL Task which runs a batch file and Send Mail Task along with other tasks. What might be the possible casue for this? What are the parameters I have to look for?

    Is that because the target DB is down at the time of run. Please give your suggestions. Let me know if you want more details.

    Thanks in advance 


    Sabi


    My guess is the batch file that is somehow sometimes not returning it's commit state.
    I've heard about this before.

    MCTS, MCITP - Please mark posts as answered where appropriate.

    2012년 4월 19일 목요일 오후 8:00
  • Hi Sabi,

    There might be various reasons to cause a SSIS package hanged for running and generally for a hang issue, there is no log information helpful for diagnostics. These are the difficulties of troubleshooting hang issues. there are three common aspects to help you perform self-troubleshooting:
    1. Resource bottleneck
    2. Blocking or Deadlock
    3. Poor performance query

    For more information about it, please refer to:
    How do I troubleshoot SSIS package hanged for running: http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/2cb0bec7-0fe2-4b8d-b59e-9534388c72e7 

    Thanks,
    Eileen

    • 답변으로 표시됨 Eileen Zhao 2012년 4월 27일 금요일 오전 6:06
    2012년 4월 23일 월요일 오전 8:54