Query timeout for Linked Server?

techresearch7777777 1,796 Reputation points
2024-04-11T03:37:43.95+00:00

Hello, I created a SQL Server 2016 Agent Job that contains 9 multiple different steps which Counts Rows from a Linked Server on 9 different tables. All steps from 1 through 8 completes succeeding pretty fast (total like 1 minute overall together) but for some strange reason when it reaches the last step 9 it seems to hang for about 10 minutes and then fails displaying the following error message:

The OLE DB provider "SQLNCLI11" for linked server "MyLinkedServerHere" reported an error. Execution terminated by the provider because a resource limit was reached. [SQLSTATE 42000] (Error 7399) Cannot fetch the rowset from OLE DB provider "SQLNCLI11" for linked server "MyLinkedServerHere". . [SQLSTATE 42000] (Error 7421) OLE DB provider "SQLNCLI11" for linked server "MyLinkedServerHere" returned message "Query timeout expired". [SQLSTATE 01000] (Error 7412). The step failed.

This is strange... my SQL Agent Job is successfully Counting Rows for all of the first 8 tables which reside within the same DB on the foreign Linked Server but last table #9 fails with above error message.

To also mention table #9 doesn't have many row counts which the size is near the same and is actually smaller/less than some of the other 8 tables.

Any ideas?

Thanks in advance.

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,749 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MikeyQiao-MSFT 560 Reputation points Microsoft Vendor
    2024-04-11T06:16:20.3333333+00:00

    Hi，techresearch7777777

    This is a common error, and we may need more detailed statements from you to determine the cause. The specific reason may be related to table locks on the linked server, or it may be associated with the transmission of overly large intermediate tables during execution.

    You can enable Trace Flag 7300 in order to get a more detailed error message from the provider. Refer to this article.

    If there are no remote linked tables, you can try to see if the statement executes successfully on the local machine.

    You can also review the execution plan of the statement to analyze the resource consumption situation.

    Hope my answer may help you.

  2. Yitzhak Khabinsky 24,946 Reputation points
    2024-04-11T21:52:52.58+00:00

    @techresearch7777777,

    Please edit you question, and add exact T-SQL query via linked server that is timing out.

    It seems that you are issuing T-SQL queries via linked server in a most inefficient way. There are 3 ways syntax wise to issue them:

    (1) Using 4-part way, like you are doing:
    SELECT * FROM ``[MyLinkedServerHere].[Country_DB].[dbo].[Location_View]
    It is a most inefficient way. ☹

    (2) Using OPENQUERY():
    SELECT * FROM OPENQUERY (linkedServer, ‘Query’)
    Much more performant than #1 above, but still not the best.

    (3) Using EXECUTE ... AT [LinkedServerName]. For example:
    EXECUTE (N'SELECT * FROM [Country_DB].[dbo].[Location_View]') AT [LinkedServerName]
    It is a most performant way!!! Highly recommended to try. 😊

    0 comments