sql agent job hangs without any errors or warnings

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » sql agent job hangs without any errors or...

sql agent job hangs without any errors or warnings

Rate Topic

Display Mode

Topic Options

Author

Message

tinnusqlserver

Posted Friday, January 18, 2013 2:51 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 4:07 PM
Points: 38, Visits: 227

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 atleast.

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

Steps Taken to Resolve issue

1) 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 a xml configuration file for connection string }

2) 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.

3) 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.

4) 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

Post #1409115

shahgols

Posted Friday, January 18, 2013 3:51 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 6:06 PM
Points: 578, Visits: 3,257

You could do a couple of things:

1. Run Profiler and trace the job.
2. Try running the steps from SSMS (if possible).
3. Create a "log" table and insert into it after every statement. This shows you on which statement the job is stuck on.

My guess is that one of the statements in your job is being blocked.

Post #1409127

opc.three

Posted Sunday, January 20, 2013 7:50 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 6,693, Visits: 11,707

Are there any Script Tasks with non-trivial programs in them? Look for code that produces a message box. Sometimes developers will leave one in there by accident and it will only execute under specific circumstances which makes the hanging-behavior seem quite random.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1409276

« Prev Topic | Next Topic »

Permissions