Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 sql agent job hangs without any errors or warnings

Author  Topic 

raj vardhan43
Starting Member

20 Posts

Posted - 2013-01-18 : 16:53:31
Hi All,

Problem:

we have a SQL Agent job which runs everyday in the morning at 4:00 am. This job hangs sometimes without providing any errors or warnings. It just hangs. BUT when i stop the job and run again it works perfectly. This happens once in a week at-least.

This job is executing a SSIS package which loads data from oracle (source) to SQL SERVER 2008 R2 (destination)

protection level for package is set to Encrypt Sensitive with password.

Steps Taken to Resolve issue

I thought the ssis package is losing connection in the middle of the process and i had increased the Connection Timeout=60 in the config file of this package but this doesn't worked. (I am using axml configuration file for connection string)
I had configured logging for this package by using sysssislog table and i had checked this table when the job hanged, it doesn't showed any error information.
I informed this issue to our network administrator to check is there any network issues, but he told that everything is fine with the network.
In the OLEDB Destination editor i had used.
Data Access Mode=Table or view-fastload
Table lock=checked
keep nulls=checked
check constraints=checked
rows per batch : blank
The above steps didn't worked yet, and it continues hanging oftenly.

Please share your opinions on this issue. I need your advice to troubleshoot this issue

raj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-19 : 02:31:56
its worth checking whether there's any other job/process thats running parallely at sametime and causing interference. Put a profiler trace on server for the time and capture results onto a file. Then analyse the executed statements for any blocking issues

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

neeraj_sharma
Starting Member

15 Posts

Posted - 2013-06-12 : 06:44:02
Thanx Vishak i had the same problem.


Neeraj Prasad

Tutorial Sql Server
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 02:12:18
quote:
Originally posted by neeraj_sharma

Thanx Vishak i had the same problem.


Neeraj Prasad



you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -