Time out Expired Error in sql server 2008 R2

Question

Dear all

We were loading the data from Source(ORACLE) to Destination(SQL SERVER 2008 R2) On Daily basis through Sql Agent Jobs

Every day we were loading Tables from Oracle to Sql server 2008 R2  after based on that tables we were Executing Reports(Source to Destination  in SSIS packges)

Sql agent job is showing an Error  on particular Report

Every day it is showing below mentioned Error on Different Report

Source: Package Connection manager "(local).kotakreports"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80004005  Description: "Timeout expired".  End Error  Error: 2012-07-18 11:41:12.85     Code: 0xC020801C     Source: xxxxxxNAME OLE DB Destination [55]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.

any body know pls guide me

Answer 1

For such issues , they are almost correlated to some specific timeout issue resulted by a clash between the T-SQL query behind report and the process of ETL (Extract Transform Load) from Oracle to SQL Server .

This clash is mostly resulted by some of assumptions :

1-      Heavy locks on the same tables shared between ETL process and reports

2-      Heavy IO /CPU overload on SQL production server resulted by ETL that exposed timeout issues for T-SQL queries in particular more the heavy ones seeking high volume of IO /CPU resources

3-      Heavy network IO asynchronous waits from Oracle to SQL due to either :

• NIC issue at any of the 2 servers Oracle or SQL
• Network communication issue at Hubs/Switches exists between them
• Network teaming issue
• Cabling issue  

Resolution:

For point #1, I do prefer to use with (nolock) or Read uncommitted transaction isolation level for SQL reports if business accept reading dirty data (Uncommitted data)

For point #2, Tuning ETL process using 2 means either T-SQL optimization or Index tuning , you might have a look at my series of blogs for this regard ending up with the below one:

http://www.sqlserver-performance-tuning.com/apps/blog/show/12927042-towards-t-sql-queries-of-0-sec-6-6-

For point #3: , I do recommend to track these network Issues from 3 aspects:

• Customize an alert for Network Asynchronous IO  waits
• Monitor Server auditing for failed logins to find out any network errors code 0x2746  or 0x40
• Use third party monitor tool such as PRTG ,Opmanager, Dynatrace/Gomez…

Kindly let me know if nay further help

 

 

---

Shehap (DB Consultant/DB Architect) Think More deeply of DB Stress Stabilities