Share via

Timeout after 10 minutes over Linked Server

Aidan Grant 1 Reputation point
2020-08-18T11:14:10.23+00:00

I have a simple select query running over a linked server. It normally takes 7 or 8 minutes, but sometimes goes over 10, in which cases it appears to time out:

Execution terminated by the provider because a resource limit was reached. [SQLSTATE 42000] (Error 7399) OLE DB provider "SQLNCLI11" for linked server "DB007" returned message "Query timeout expired". [SQLSTATE 01000] (Error 7412). The step failed.

I've searched for the reason for this, and the popular answer seems to be that it's down to the "Remote Query Timeout" setting on the server being linked (DB007), which I have set to zero, but the issue still occurs.
Connection timeout and Query Timeout on the actual linked server are also set to zero.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,967 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. David Browne - msft 3,851 Reputation points
    2020-08-18T12:41:52.77+00:00

    There's also a server-wide linked server query timeout. See Error message when you execute a linked server query in SQL Server: "Timeout Expired"

    You can allow linked server queries over 10min like this: 

     sp_configure 'remote query timeout', 0 
 go 
 reconfigure with override 
 go
    0 comments
  2. AmeliaGu-MSFT 13,981 Reputation points Microsoft Vendor
    2020-08-19T05:57:21.807+00:00

    Hi aidangrant,

    In addition, could you please check if there are any blocking on the remote server?

    Please refer to this article which might help.

    Best Regards,
    Amelia

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.