CODEWITHSUNDEEP
sqlsql-serverlinked-server
Jay
Jay

Reputation: 910

Resource limit on SQL Server Linked Server

I'm frequently receiving the error on a stored procedure that uses openquery to read via a linked server.

The OLE DB provider "SQLNCLI11" for linked server "BrackleyICS" reported an error. Execution terminated by the provider because a resource limit was reached.

This will usually happen at 10.01 minutes. This would imply a timeout setting, however on other occasions it will run fine taking 35 minutes to complete.

Has anyone encountered this?

Upvotes: 4

Views: 27443

Answers (3)

Pawel Czapski
Pawel Czapski

Reputation: 1864

You can check you current timeout settings by:

query timeout

right click server > Properties > Connections > Remote Query Timeout

login timeout

right click server > Properties > Advanced > Remote Login Timeout

I think your login timeout is set to 10 mins, you need to increase this by running below script, change value from 30 seconds to required one

sp_configure 'remote login timeout', 30
go 
reconfigure with override 
go

Reason why it is not timeouts every time: Not sure but if user ids logged on to server then timeout doesn't happen.

Upvotes: 4

alekuz
alekuz

Reputation: 1

EXEC sys.sp_configure N'remote query timeout (s)', N'1800' GO RECONFIGURE WITH OVERRIDE GO

The default value is 600 s which is equal to 10 min

Upvotes: 0

user170442
user170442

Reputation:

Linked server has also its own Query Timeout setting in Linked Server->Properties->Server Options. It is likely it is set to 0 - which is default value.

In this case it is using Query Wait advanced server setting - which again - most likely is set to -1 (default).

In this case timeout is decided per query and it is calculated as 25 times of estimated query cost.

More info in MSDN

Upvotes: 0

Related Questions