DTS Job stalling, running for 60 hours...

[christer99]

I have a DTS job that runs every hour. The last few weeks, it is stalling maybe once or twice per week. The job is not failing and that's the problem. It is just saying executing and it will sit there forever. There is no error or any information in the job log history. The only way to stop the job, is to cancel the job. Then if I look in the job history it will tell me that the job was cancelled (after 60 hours) and none of the steps have been completed. There is no error or other information. How will I know if the job is stalling? I know there are email notifications if the job if failing, but it is not failing, it is just stalling. Can anyone help? I am desperate. By the way, it is the same DTS job that stalls once or twice per week, and the job is importing dbase into SQL.

 

[mrdenny]

Take a look at sp_who2 and see if anything is blocking the code that the DTS package is running.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jeffmoore64]

Hi,
I've been having the same problem. I have a package that (used to run) is hanging. When I run the SQL that is in that step in Query Analyser it runs in 2 seconds!!! There are no locks or blocks shown in sp_who2.
HELP US!!!

 

[mrdenny]

Do you see the DTS package command when you run sp_who2? It should tell you why it's waiting.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mlawrenson]

Did you ever get anywhere with this? I have got the exact same problem with one of my DTS jobs.

 

[christer99]

well it hasn't happened in a couple months, but i never found out why or a solution to the problem.