2

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

Improve this question
5
  • Do you call of xp_cmdshell in your job? What is the wait type in your ssis package? The links below might be helpful:
    – Sky
    Commented Jan 20, 2013 at 0:48
  • 1
    Sorry, I was typing the comments above from my mobile and made mistakes, which the forum did not let me to delete or edit them after 5 minutes. Do you have call of xp_cmdshell in your job? Is it possible that you are trying to use a resource that is not available at time of running Or the user you have set up to run the job (scheduled) does not have the required rights to access the resource? Also what is the wait type in your ssis package? Is it PREEMPTIVE_OS_PIPEOPS? If so, please check the link below: sqlservercentral.com/Forums/Topic802987-110-1.aspx#bm1339542 The links below al
    – Sky
    Commented Jan 20, 2013 at 1:06
  • 1
    What's the wait type of the running process in the SQL Server? Is the query from the SSIS package being executed against the Oracle server when it "hangs"?
    – mrdenny
    Commented Jan 21, 2013 at 19:19
  • hi nazila!! we don't have call to xp_cmdshell in our job. I guess "it is possible that i am are trying to use a resource that is not available at time of running". The user set up to run the job have all the required rights(Administrator). I have to check wait type. Thanks for your time
    – oOo Testing1 oOo
    Commented Jan 22, 2013 at 1:49
  • hi mrdenny!! the query is executed against oracle server when it hangs!! I didn't know about wait types... i will check wait types now when it hangs... thanks
    – oOo Testing1 oOo
    Commented Jan 22, 2013 at 1:56

1 Answer 1

Reset to default
0

I have a similar problem in SQL Server 2008. We have hourly schedule which pulls data from multiple Sybase Adaptive and Sybase IQ servers and populates the SQL Server (Using System DSN, Linked Server). Every Friday after 2 AM those jobs hang. After a little bit of research on my Windows Server which has the SQL Server I found that the McAfee weekly schedule runs every Friday from 2 AM.

As a short term solution, the anti virus scan can be cancelled and restart the SQL Server Service to clear the hang.

For long term, our security team has advised us to remove McAfee and load Kaspersky.

A note here.. during the anti virus scan only those jobs hang which pulls data from Sybase (as mentioned above), all other jobs will run perfectly fine.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.