Getting an Intermitent issue with Linked Server between 2 On-premise SQL Servers

Shivendoo Kumar 736 Reputation points
2020-08-24T03:22:38.75+00:00

Hi All,

Every now and then I am getting this issue and not sure where to look into it..?

_Master_ODS:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Login timeout expired".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Unable to complete login process due to delay in login response".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "TCP Provider: Timeout error [258]. ".

I have SSIS Package running from SERVER1 and it has a query (view /SP) which has linked server to connect to another Server2. Those packages often fail with above error.

I got this link: https://samzsimplesql.wordpress.com/2015/09/01/troubleshooting-connectivity-issues-timeout-error-258-unable-to-complete-login-process-due-to-delay-in-prelogin-response-pre-login-handshake-failed-2/

going to check with DBA and infra guy to follow steps to find out root-cause.

Please can you guide me. I checked the SQL error Log on Server2 and can't find anything that can be investigated further.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,757 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,554 questions
0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. m 4,271 Reputation points
    2020-08-24T06:59:58.69+00:00

    Hi @Shivendoo Kumar

    Every now and then I am getting this issue and not sure where to look into it..?

    Is your OS Linux or Windows?
    What is your sqlserver version? Do you test with the latest CU?
    Execute code as next to get the result.

    select @@version

    More information:
    https://support.microsoft.com/en-us/help/4499423/fix-performance-issues-and-timeouts-may-occur-while-connecting-with-sq
    https://stackoverflow.com/questions/21603897/dts-e-oledberror-error-code-0x80004005-difference-between-sql-native-client-an

    You can also google the similar issues to have a search.

    BR,
    Mia
    If the reply is helped, please do “Accept Answer” .

  2. Erland Sommarskog 101.4K Reputation points MVP
    2020-08-24T21:49:10.063+00:00

    It certainly looks like a networking problem to me. Keep in mind that one component here is the AD, so that communication also needs to be investigated.

    0 comments
  3. m 4,271 Reputation points
    2020-08-25T01:25:45.36+00:00

    Hi @Shivendoo Kumar ,

    Is the reply helpful?

    BR,
    Mia
    If the reply is helped, please do "Accept Answer".--Mia

    0 comments
  4. Tom Phillips 17,716 Reputation points
    2020-08-25T20:08:41.64+00:00

    This is likely something happening at that same time causing the server to become unavailable during that specific time, backup or maintenance or something. The first thing I would try is to schedule it for a 30 mins later and see if it works.

    0 comments
  5. m 4,271 Reputation points
    2020-08-26T01:30:21.233+00:00

    Hi @Shivendoo Kumar ,

    Is the reply helpful?

    BR,
    Mia
    If the reply is helped, please do "Accept Answer".--Mia

    0 comments