DTS job never completes
-
Hi All,
I have found a DTS job showing its state as " performing completion actions". It has been in this state for two whole days and my idera diag manager has sent an alert stating that the job has failed.
I am not able to stop/kill the job. I have checked in the sysprocesses table and killed all the related processes but still it shows the same state.
What I did is scripted the job, renamed the original job created a new job with original name and deleted the old job.
This does not seem to be logical to me. Does anybody have any solutions or recommendations to this ?
Any help would be greatly appreciated
Regards,
Phani
-
The DTS package is probably awaiting for a response to message prompt at the server console. eg: MsgBox prompt in VBScript.
If you can't log into the server console directly, or the service is set to not have desktop interaction, the you'll most likely have to restart the SQL Services.
--------------------
Colt 45 - the original point and click interface
-
There could be several reasons for the unresponsive nature of DTS packages.
Reason 1: If you have Sql Server 2000 running on Windows 2000 with a service pack < 3, then there is a bug within Sql Server 2000 which falters for a transformation task if it includes different connection objects.
Resolution: Apply service pack 6a to Sql Server 2000.
--------------------------------------------------------------
Reason 2: If the ActiveXScript includes an infinite loop.
Resolution: Check for FOR, WHILE loops which have some kind of iterative logic.
--------------------------------------------------------------
Reason 3:Check the status of an CustomTask. If it has been set to WAITING for some task to finish then it might wait indefinitely.
Resolution: Check for any WAITING task
------------------------------------------------------------
Reason 4: Check the return statement of the ActiveXScript Task. This should be generally _SUCCESS or _FAILURE.
Hope this helps.
-
I've had the same problem when it got hung on an email step. When this happens, you need to restart the SQL Agent and it will clear it out.
-
when I run EXEC MSDB..SP_get_composite_job_info, This jos shows current_execution_status as 7.
The link http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_cnst04_5cfn.asp says 7 means "All executable job steps have completed. Job history logging is being performed" but this never ends.
I am out of ideas. Please help me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply