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.
| 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/
|
 |
|
|
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 |
|
|
|
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
|
|
|
|
|
|
|
sql agent job hangs without any errors or warnings