SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 sql agent job hangs without any errors or warnings
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raj vardhan43
Starting Member

USA
20 Posts

Posted - 01/18/2013 :  16:53:31  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/19/2013 :  02:31:56  Show Profile  Reply with Quote
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

India
15 Posts

Posted - 06/12/2013 :  06:44:02  Show Profile  Reply with Quote
Thanx Vishak i had the same problem.


Neeraj Prasad

Tutorial Sql Server
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/13/2013 :  02:12:18  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000